Free SQL Help for Devs, DBAs, and the Curious

The world of SQL servers has seen significant advancements over the years, one of which is the transition from Data Transformation Services (DTS) to SQL Server Integration Services (SSIS). Many businesses still have legacy DTS packages that need to be converted into SSIS to optimize their database management systems. This process can be a daunting task, especially for larger systems, but with the right knowledge and tools, it is manageable. This article will guide you on how to handle legacy DTS conversions to modern SSIS using SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL.

Before starting the conversion process, it is crucial to understand the differences between DTS and SSIS. DTS, the predecessor to SSIS, is a set of objects using an ETL tool that extracts data, transforms it, and then loads it into a destination. On the other hand, SSIS is a component of Microsoft SQL Server and a more advanced platform for data integration and workflow applications, which offers a wider variety of transformations and functionality.

The first step in converting DTS to SSIS is to analyze and document your existing DTS packages. This step can be performed using the DTS Designer, a graphical tool that comes with SQL Server. Here is a basic command to list all DTS packages in SQL Server:

-- SQL Server T-SQL
SELECT * FROM sysdtspackages90

The next step is migrating DTS packages to SSIS. This can be done with the SSIS Migration Wizard that comes with SQL Server 2012 and 2014. Note that the SSIS Migration Wizard will not convert some tasks and transformations, such as ActiveX scripts and Dynamic Properties Tasks. These will need to be manually converted.

In some cases, you may need to rewrite your DTS packages to make them compatible with SSIS. This can be a complex task, especially if you are dealing with a large number of packages. For this reason, it is recommended to use a migration tool, such as the DTS xChange from Pragmatic Works, which automates the conversion process and makes it more efficient.

For databases running on MySQL 5.6 or DB2 10.5, the process of converting DTS to SSIS is slightly different. You will need to use the respective data source connectors for MySQL and DB2 in your SSIS package. Here's an example of how to connect to a MySQL database in SSIS:

-- MySQL
CREATE DATABASE my_db;
USE my_db;
CREATE TABLE my_table (my_column INT);

Finally, for databases hosted on the cloud like Azure SQL, the process is similar but you need to ensure that your network and security settings allow SSIS to connect to your Azure SQL Database. Also, you should use the most recent version of SSIS that supports Azure to ensure compatibility.

In conclusion, converting legacy DTS packages to modern SSIS can be an intricate process, but with a good understanding of both systems and the right tools, it can be done effectively. The key is to thoroughly analyze your DTS packages, use the appropriate migration tools, and be prepared to do some manual conversion for certain tasks and transformations. Happy migrating!