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.