Database, SQL Server

Migrating DTS to SSIS Part 1

SQL Server 2005 has definitely evolved SSIS into a major product in the ETL market. It was a total code rewrite from SQL Server 2000 DTS. Best part is that it comes bundled with the SQL Server license.

We usually get confused about SSIS as a new version of DTS but everyone should clearly understand that DTS and SQL Server Integration Services (SSIS) are completely different applications. There is very little code share between the two applications. The entire architecture is very different. Integration Services takes DTS to a true enterprise level Integration and Extract, Transform and Load (ETL) platform. In short, it should be very clear that although SSIS was inspired by the concept of DTS, it is a totally new product.
SQL Server 2005 till now supports DTS from a runtime perspective, but the next release of SQL Server may not carry this support. This gives you a small window of opportunity to upgrade your packages from DTS to SSIS. You can still continue to run your DTS packages from a SQL Server 2005 but it should not stop you from migrating.

Area of Differences

This will uncover some of the key differences between Integration Services and Data Transformation Services. There is a noticeable change in the designer tool which has been greatly improved and bundled with lots of new tasks and transforms.

Feature DTS Solution SSIS Solution Comments
Data Transformation Limited Transforms and ActiveX script with pump Various Transformations, error outputs and easy extensibility with custom components DTS transforms were limited and ActiveX was slow. ActiveX transformations cannot be migrated
Control Flow Loopingp ActiveX Scripting using DTS object model Built-in Loop container objects with stock enumerators DTS has no built-in looping feature and users had to use ActiveX to achieve looping. This cannot be migrated.
Package Initialization and Configuration Dynamic Properties Task and ActiveX Script Task Package Configurations and Property Expressions The Dynamic Properties Task and ActiveX Script Task followed the package object model to configure other tasks. SSIS does not allow tasks access to the object model.
Custom behavior in packages Custom behavior in packages The Script Task can only be used for task functionalities and cannot access the object model or other tasks. Tasks accessing the object model are generally difficult to support and upgrade.

Here is a table which shows the success probability

DTS Task Equivalent IS Task Success Probability
Analysis Services Analysis Services Processing 0%
Dynamic Properties Script Task 0%
ActiveX Script ActiveX Script 40%
Transform Data Data Flow 40%
Data Driven Query Execute DTS 2000 Package 40%
File Transfer Protocol File Transfer Protocol 90%
Execute SQL Execute SQL 95%
Execute Process Execute Process 95%
Execute Package Execute Package 90%
Copy SQL Objects Transfer SQL Objects 90%
Send Mail Send Mail with SMTP 95%
Bulk Insert Bulk Insert 70%
Message Queue Message Queue 80%
Transfer Error Messagest Transfer Error Messages 95%
Transfer Database Transfer Database 85%
Transfer Master SP’s Transfer Master SP’s 95%
Transfer Jobs Transfer Jobs 95%
Transfer Logs Transfer Logs 95%
Global Variable Variable 100%

Note: Although the tasks may migrate, they may not work as expected. Your results may vary based on the type of work you are doing within your DTS packages.