Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Working with DTS in SQL Server 2008

By Dale Parsons,

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.

Total article views: 9560 | Views in the last 30 days: 6
 
Related Articles
FORUM

Program to install on database servers

Program to install on database servers

BLOG

Microsoft DreamSpark Program

Back in 2008, Microsoft rolled out the DreamSpark program as a means to get Microsoft software desig...

FORUM

Microsoft SQL Server Native Client install

Microsoft SQL Server Native Client install on 32 bit windows application server for 64 bit database ...

FORUM

Add on packages to install with SQL Server 2005

Need advice on what add on packages to install with the standard SQL Server 2005 install.

ARTICLE

Beginning SQL Server 2000 Programming

A review of Beginning SQL Server 2000 Programming

Tags
64-bit    
dts    
sql server 2008    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones