It’s a Bird…

No, not really.   It’s just Windows 7.   I have recently upgraded to Windows 7.  I took the roundabout trip to do that going from Server 2003 to Windows XP and then finally up to Windows 7.  Having never used Windows Vista, there were several nuances to learn.  Most of those nuances had to deal with security and how to disable this or that setting or run programs with elevated privileges.  Pretty straight forward for much of it, once you figure out that it needs to be done.

One area that caused me a lot of grief trying to get it to work correctly was in the realm of dealing with DTS packages.  I browsed several webpages trying to find the fix for this particular issue.  Most of the fixes were explicitly designed to fix the issue in a 64 bit environment.   I am running a 32 bit environment.  I must admit that the packages run just fine from the 64 bit servers that are running SQL 2008 on Windows 2008 R2.  However, running the package or editing the package from my laptop only resulted in the following error message.

SQL Server 2000 DTS Designer components are required to edit DTS packages.  Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

Nitty Gritty

Let’s quickly delve into everything that I did in an effort to fix this.  Let’s start with the most comprehensive resource I found on the issue.  You can find that resource here.  The information presented in that blog post is more informative than the information listed in MSDN on the same topic.  I will outline the steps from the first article noted.

1. Install Sql Server 2000 DTS Designer Components
   1. Download from here.  This is the most current version as of the writing of this article.
2. Install Sql Server 2005 backward compatibility components
   1. Download from here.  This is the most current version as of the writing of this article.
   2. This is available with the installation media for SQL Server 2008.
3. Verify your path environment variable.  The SQL 2000 path should be placed in the string prior to the SQL 2008 variable.  As a sample, this is what mine looks like.
   1. %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\
   2. You can access the environment variables in Windows 7 by: Right Click My Computer  -> Properties -> Click Advanced System Settings -> Click Environment Variables -> Scroll to “Path” -> Click “Path” and then click Edit…
4. SSMS – Manually Copy Files
   1. DLL Files to copy
      1. semsfc.dll, sqlgui.dll, sqlsvc.dll
   2. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\
   3. Destination
      1. %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\
      2. %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\
   4. RLL Files to copy
      1. semsfc.rll, sqlgui.rll, sqlsvc.rll
   5. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
   6. Destination
      1. %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\
5. BIDS Manually Copy Files
   1. DLL Files to copy
      1. semsfc.dll, sqlgui.dll, sqlsvc.dll
   2. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\
   3. Destination
      1. %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\
   4. RLL Files to copy
      1. semsfc.rll, sqlgui.rll, sqlsvc.rll
   5. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
   6. Destination
      1. %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%
         1. I had to create the Resources Subdirectory
      2. %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\%lang_id%\
6. The last step was not applicable in my case since the file version already matched
   1. stardds.dll File version: 2000.80.2151.0

That is the basic gist of what needs to be done in most cases to resolve this issue.  In my Step 5 (BIDS manual copy), I would note that this should be a required step.  I skipped that step numerous times due to it appearing to be “optional.”  I was not attempting to edit these packages in BIDS, but directly from SSMS.  It was only after trying to edit the package in BIDS that I found the next key to the issue I was experiencing.  Up to that point, I had installed, uninstalled, rebooted and repaired the install numerous times after several different minor tweaks.  Only after the next step did it finally start working.

I must also share that I attempted starting SSMS as administrator and also tried to launch it in compatibility mode.  I had to verify that neither of those options was the root of my problem.

Auction Block

I think it is necessary to describe how to edit a DTS package from BIDS prior to explaining what the fix was that got this working for me.  You don’t just open a DTS package directly in BIDS.  In my case, the DTS packages are not structured storage files either – I have them stored in SQL Server.

In BIDS, there is a Control Flow item called “Execute DTS 2000 Package Task.”  Drag this item into the work area in BIDS.   This Control flow task is illustrated in the image to the left as the last option in the image.  This task will give you the ability to edit a SQL 2000 dts package from within SSIS.  With the task showing in your workspace, open the properties for the task so you can configure it appropriately for the dts package you wish to edit.

After setting the properties for the package that you desire to edit or even simply view, click the Edit Package… button.  It is at this point that I was able to get a further clue into what was causing the inability to open packages from within SSMS.  At this point I got the following error message.

Error: Attempted to read or write protected memory. This is often an indication that other memory is corrupt (Microsoft Visual Studio).

Could this be my big break in the case?

Hard Hat and Hammer

With this piece of information, I have a tool and I can get to work fixing the problem.  Admittedly, at this point, I did not know that this was the root of the problem for me.  Quickly I race to my search engine du jour in an effort of finding any relevant information on this new error message.  I soon discover that this issue is one that existed with Windows Vista and appears occasionally in Windows 7.  The cause of this problem has its roots in Data Execution Prevention.  It would seem to only be an issue if your CPU supports DEP.   To determine if you have DEP enabled, you will need to check the Advanced Settings of your computer properties.  You can get there as explained earlier when checking the path.  Only this time you will need to click the Settings… button rather than the Environment Variables button.  Once inside of the settings, you will notice that there is a tab called “Data Execution Prevention” – click on this tab.

Once you have reached the DEP tab, you will be able to tell if DEP is enabled or not by the display on the screen and not by the settings selected.  To the left, I have a screenshot of how mine looks at present.  All settings are greyed out and I see a message at the bottom explaining that I must enable it via a command prompt utility called bcdedit.exe.

If DEP is enabled, the options will not be greyed out.  You may also see a note at the bottom that says “Your computer’s processor supports hardware-based DEP.”  You will also have a choice at the top between two settings, both of which are “ON.”

Just as you must enable this setting through the command prompt, you must also disable it through the command prompt.  Once disabled or enabled, you must reboot the computer for the settings to take effect.  So how does one disable this setting?  Here is the command to disable that setting.

bcdedit.exe /set {current} nx AlwaysOff

Should you decide you need to re-enable the DEP setting, you can use the following command.

bcdedit.exe /set {current} nx AlwaysOn

Disclaimer: I recommend you have a good backup of your system first.  It is also advisable to have a system restore point and understand how to boot into safe mode.  Adjusting settings like this may have an effect that is undesirable (such as unable to boot into windows).  You can read more about DEP from Microsoft.

I got my Toes in the Water…

As you can already see, I have disabled DEP on my laptop.  After disabling DEP and having rebooted, I am now able to edit a DTS package from within BIDS.  Great! Can I open a package from within SSMS though?  I open SSMS and attempt to open a package and it does indeed work.  This is just one more tool in the troubleshooting arsenal.  Happily I can now open and edit packages from both tools.  This will permit me to take the time I need to upgrade those packages to SSIS and do it correctly.