What needs to be done to be able to edit DTS 2000 packages on a Windows 2008 or Vista Machine ?
This question has often been asked by customers who have a lot of DTS packages and want to continue running these packages on SQL 2008 servers running on Windows 2008 or Vista machines while they work on redesigning the packages to use the new features of SSIS in SQL 2008 .
The steps in Books online work if the Operating System is Windows 2003 or Windows XP . For Windows 2008 or Vista , the following steps will need to be used to be able to successfully open and edit DTS 2000 packages .
1. Download the X86 Package for Microsoft SQL Server 2005 Backward Compatibility Components.
2. Download the X64 Package for Microsoft SQL Server 2005 Backward Compatibility Components.(This step is only required if you wish to edit DTS 2000 package on a X86 machine (Windows 2008 or Vista)
3. Download the X86 package for Microsoft SQL Server 2000 DTS Designer Components
4. Logon to the server where you would like to be able to edit the DTS 2000 package and run the package downloaded from Step2 (assuming that this will be Windows 2008 with SQL 2008 in 64 bit . Make sure the X86 package is in the same folder as the X64 package . Setup needs to copy files from the X86 package) IF the machine already has the BC components for SQL 2005 installed ,setup will prompt you if you wish to remove or repair , choose to repair. This step is not required if the machine is X86
5. Now run the package from step 3 . IF there is a version already installed on the machine , you will be prompted to upgrade or cancel , choose to upgrade.
6. Depending the Architecture of the OS , choose from a) or b)
a) For X86 Machines , do the following
1. Open a CMD prompt with Admin privileges , and type "Set LANGID =1033" . ( Depending on the locale , you might need to change the value to the ID for the local language , 1033 is for US English)
2. Execute the following commands
Copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%LANGID%\semsfc.rll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%LANGID%\sqlgui.rll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%LANGID%\sqlsvc.rll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\semsfc.dll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlgui.dll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlsvc.dll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%LANGID%\semsfc.rll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%LANGID%\semsfc.rll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%LANGID %\sqlgui.rll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%LANGID%\sqlgui.rll
copy %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%LANGID%\sqlsvc.rll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%LANGID%\sqlsvc.rll
b) For X64 Machines , replace %Program Files% from the above commands with %ProgramFiles(x86)% . Rest remains the same.
Now launch SSMS.exe and attempt to open a .dts file. Similarly, from BIDS, see if you can embed a DTS 2000 package in a Execute DTS 2000 package task. Make sure to change the setting for Run64BitRuntime to False from the Solution properties. This is because BIDS is 32 bit and the default on X64 machines is to run the debugger in X64 bit mode. DTS 2000 runtime exists only in X86 mode.
SQL 2000 has not been tested on Windows 2008 or Vista and so has limited or no support on these versions of the Operating System. The DTS 2000 Designer is a SQL2000 component, so the support for this component is tied to the support policy for SQL 2000.
Prashant Bhat, SQL Server Dedicated Support Engineer