There are many of you SQL Developers and wizards out there still working on legacy DTS packages. Of course, we cannot live without SSMS (SQL Management Studio) and thus try to integrate the legacy SQL 2000 DTS Designer into it. But, life is not that easy and the entire procedure to get DTS Designer/Runtime working on SQL 2005/2008/R2 Management studio is quite cumbersome.

While I was working on setting this up myself, learnt quite a few tricks which I what this blog is going to talk about. At the end of this you should be able to both import, design and execute legacy SQL 2000 DTS packages (not SSIS) on a machine that has SQL 2005+ Management Studio installed. Read on and let free your picassonian designer skills with DTS packages.

Getting Started
First off, here is the link to the official documentation that talks about the support for SQL 2000 DTS packages on SQL 2008 R2. There are 2 types

1. Run-time support  (meaning I can execute DTS packages using dtsrun.exe)
2. Design-time support (meaning I can import, open & design DTS packages in SSMS)

How to: Install Support for Data Transformation Services Packages
http://msdn.microsoft.com/en-us/library/ms143755.aspx

Read the above article to get an understanding of what is possible and what is not (Don’t start/install anything yet) as this process can be quite tricky. My friends @ SQLServerFAQ have a nice post on this which is also something I would recommend reading here.

Ready, Set, GO!

 

Step-by-Step Instructions

1. You would need to install a few additional components, so please download the following packages and keep them ready.

a. Microsoft SQL Server 2000 DTS Designer Components
http://www.microsoft.com/download/en/details.aspx?id=11988

Download the package SQLServer2005_DTS.msi

b. SQL Server 2005 Backward Compatibility Components

Depending on the version of your SQL Server Management Studio (SSMS) you will have to download the corresponding package

SQL 2005 – http://www.microsoft.com/download/en/details.aspx?id=15748

SQL 2008/R2 – http://www.microsoft.com/download/en/details.aspx?id=8824

Download the package SQLServer2005_BC.msi. If you have a 64-bit machine, then you would need the x64 package because the x86 package will not let you install and you will get this error.

c. Make sure when you had installed Management Studio that you have chosen “Management Tools – Complete” during the setup for SQL 2008.

 

2. Install both the above packages in the same order as listed above. Once this is installed you will see that the following paths containing some DLL’s

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn \
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\

3. Now, you need to edit the system PATH to ensure that the path for “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\” comes before the ..\90\Tools or ..\100\Tools folders. You can do this by going to My Computer –> Properties –> Advanced –> Environment Variables and editing the value for "PATH” as shown below.

Here is a sample path value from my working machine.

PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Windows\System32\WindowsPowerShell\v1.0\

4. Okay, now comes the manual part. This step is the same as given in the MSDN article.

 

32-bit Machine (x86)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

64-bit Machine (x64)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

On a system with default settings,

%ProgramFiles(x86)% –> C:\Program Files (x86)\
%ProgramFiles% –> C:\Program Files\

 

5. After this is done I would suggest a reboot of the machine to ensure that if any pending file renames are present, they will be completed on a system restart. This is not mandatory, but just a suggestion. If you cannot reboot, then you need to close all instances of SSMS and re-open to test opening a DTS package.

TESTING

From SQL Server Management Studio, navigate to the Legacy folder in Object Explorer as shown below

If all goes well, then when you open a SQL 2000 DTS package (test.dts), then you will see the legacy DTS designer open up as shown below.

Still Not Working
If things go wrong, then you will get this error when you tried to open a DTS package,

Error Text

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)

If you have faithfully followed the above procedure step-by-step then you will need to perform this additional step as well on those systems where you receive the above error. This is why I have not added this step as default since it does not occur on all systems.

1. Navigate to C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn and see if there is a file called stardds.dll present there.

2. Note down the file version, date and size.

3. If your Operating System is Windows Server 2008/R2 or Windows Vista/7, then you will need to replace the file present with the one given below. This is because Windows 2008+ (including Windows 2008 R2) requires the 2005.90.4035.0 version of stardds.DLL. Windows 2003 or earlier requires the 2000.80.2151.0/2282.0 versions of stardds.dll

a. Rename the existing stardds.dll to stardds.dll.original

b. Download the version of stardds.dll that applies to your OS

For Windows 2008/2008 R2/Vista/Win7 –> Download stardds.dll.4035  from HERE
For Windows 2003/2003 R2 –> Download stardds.dll.2282  from HERE

c. Copy the downloaded DLL to the location C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\ (for 64-bit systems) OR C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ (for 32 bit systems) and name it as stardds.dll

4. Close SQL Management Studio and re-open it and repeat the process of opening the DTS package and it should work now.

Caveat for DTS Packages contains Analysis Service (AS) Tasks
Apart from this, if any of the DTS packages have Analysis Services Cubes then you will still not be able to open these packages in SSMS. You will be faced with this error message

Error Source : Microsoft Data Transformation Services (DTS) Package
Error Description : Invalid class string

The backward compatibility package do not include design-time support for modifying DTS packages that contain the SQL Server 2000 Analysis Services Processing task and also do not include support for the SQL Server 2000 Data Mining Prediction Query task. So, if your DTS package has either "AS Processing Task" or "Data Mining Prediction Query task", then we cannot open/design these packages in SSMS 2008/R2

If you want to be able to do so, then you will have to install the following SQL Server 2000 components from SQL Server 2000 media

1. Analysis Manager
2. Decision Support Objects
3. Client Components

You don’t the AS Server component, just the above will suffice. Once you install these components you then did the get the following DLL’s present on the machine,

msmdtsp.dll   –> for Analysis Services Processing Task
msmdtsm.dll  –> for Data Mining Prediction Task

Close and re-open SSMS and you should be able to open the package now.

 

Phew! This was quite a long post indeed but I think I have covered most issues one could run into. If you come across any other issues, leave a comment behind and I will try and see if that can be resolved.

As always stay tuned to the TheSQLDude for more….