Free SQL Help for Devs, DBAs, and the Curious

Legacy DTS (Data Transformation Services) packages can be a challenge to work with on modern SQL Server versions like SQL Server 2016 and 2017. They are no longer natively supported, but with a few workarounds, it is possible to continue using them, albeit with some limitations. This post will guide you through the steps of working with legacy DTS packages on modern SQL Servers.

First, it is important to note that Microsoft has replaced DTS with SSIS (SQL Server Integration Services). However, legacy DTS packages can still be run on SQL Server 2016 and 2017 by using DTS runtime. The first step in this process is to install the Microsoft SQL Server 2005 Backward Compatibility Components. This will allow the server to understand and execute DTS packages.

Once the Backward Compatibility Components are installed, DTS packages can be executed using the DTSRUN command. Here's an example of how to do this:

EXEC master..xp_cmdshell 'DTSRUN /S yourServer /U yourUser /P yourPassword /N yourDTSpackageName'

The next step is to consider migrating the DTS packages to SSIS. This can be a complex process, but Microsoft provides a tool called DTS Migration Wizard that can help. It is important to note that this tool may not be able to convert all aspects of a DTS package, so manual intervention may be required.

Working with DTS on DB2 11.1 or MySQL 5.7 presents its own challenges. These databases do not natively support DTS, so a different approach is required. One method is to use a third-party tool, like the KingswaySoft's SSIS Integration Toolkit, to import and export data between the databases and the DTS packages.

For cloud-based databases like Azure SQL, DTS packages can be run by using a hybrid connection from an on-premises SQL Server. This allows the DTS packages to access the Azure SQL database as if it was on the same network. Here's an example of how to create a hybrid connection:

EXEC sp_addlinkedserver @server = N'Azure', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'yourAzureSQLServer.database.windows.net';

In conclusion, while working with legacy DTS packages on modern SQL Servers can be challenging, it is certainly not impossible. Whether you choose to run the packages using DTS runtime, migrate them to SSIS, or use a third-party tool, there are several viable options for keeping your legacy DTS packages running on modern SQL Servers.