Recently, we were faced with the not uncommon situation of upgrading our SQL 2000 production system directly to SQL 2008 (and bypassing SQL 2005). We knew pre-upgrade that the functionality of many of our DTS packages would soon become obsolete (in a few months) and so this did not warrant the time and expense of converting them to SSIS. If you are planning an upgrade to SQL 2008 and are not ready to head down the SSIS path here are some ideas to accommodate your existing DTS packages.
WORKING WITH DTS IN SQL 2008 64-BIT VERSION
Like many companies, we wanted our new production servers to gain the performance increases from running the 64-bit edition of SQL 2008. So while it is possible, on a 64-bit machine, to install a 32-bit version of the dtexec utility (dtexec.exe) that allows you to run the DTS packages in 32-bit mode, the utility does not allow you to edit the packages.
One option around this limitation is to edit the DTS package locally or on a 32-bit server and then copy the package over to the 64-bit machine. Note: Remember to change the Source and Destination connections where necessary to the new 64-bit machine.
We did not like this option as there are times when you want to run packages interactively and be able to edit them for troubleshooting purposes. We simply chose to continue hosting the DTS packages on our old production 32-bit SQL Server 2000 box and simply change the Source and Destination connections to the new 64-bit SQL 2008 environment. Nothing prevents you from doing this, and we have found the performance of this new configuration to equal that of our previous setup.
WORKING WITH DTS IN SQL 2008 32-BIT VERSION
In order to edit and run DTS packages within SQL 2008 Microsoft SQL Server Management Studio (SSMS) you will need to install the Microsoft SQL Server 2000 DTS Designer Components. Best to refer to the Microsoft link http://msdn.microsoft.com/en-us/library/ms143755.aspx for instructions as the installation procedure is not the smoothest. Even after installing the Client Tools Backward Compatibility and the SQLServer2008_DTS.msi you still may get the error below when you attempt to open a package.
"Unable to load SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, run the SQL Server Installation Wizard. On the Components to Install page, click Advanced, then select Legacy Components for installation. (Microsoft.SqlServer.DtsObjectExplorerUI)"
As per the instructions in the link we found we had to copy the various .dll and .rll files from:
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
(Taken from the Microsoft link http://msdn.microsoft.com/en-us/library/ms143755.aspx).
Where are the DTS packages hiding in SSMS?
The DTS location is not readily apparent but in order to view the DTS packages in SSMS you need to expand the Management folder and then in turn expand the Legacy folder which contains the Data Transformation Services folder (see below). Note: Double clicking the package will not actually open it. The package needs to be right clicked and then (if there are multiple revisions) select the appropriate version.
Conclusion
In a perfect world when you are ready to roll out SQL 2008 you are in a position to dispense with your legacy DTS packages and implement your brand spanking new SSIS. Should you still need to continue to use the DTS packages I have discussed how this can be done in both 32-bit and 64-bit SQL 2008 environments.