Migrating from Data Transformation Services to Integration Services in SQL Server
- The Scope of Things
- Available Migration Resources
- Some Migration Considerations
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
- The Scope of Things
- Available Migration Resources
- Some Migration Considerations
"WHAT HAVE YOU DONE WITH MY DTS?!"
—DARREN GREEN
The Scope of Things
When presenting Integration Services (IS) to current or former Data Transformation Services (DTS) users, one of the first things they want to know is how Integration Services will affect their current packages. The questions come in different forms, such as the following: Will it break my current packages? Can I run IS side by side with DTS? Can I continue to use my DTS packages while building new IS packages? or Can I upgrade my DTS packages to IS packages? But, essentially, what people are asking is, "What's the migration story?"
Some of the answers are simple; some are complex. For the most part, the story is pretty positive and much of the difficulty with upgrading is related to the decisions you must make about when and how to do the migration. You also have some good options for retaining your current investment in DTS packages.
This chapter explains the available resources for performing migrations and some of the problems you might encounter. By the end of the chapter, you should have answers to the previous questions and a better idea how to proceed with your upgrade and migration.
SSIS Is Not DTS
The first thing everyone should clearly understand is that DTS and SQL Server Integration Services (SSIS) are completely different applications. If you were to draw rudimentary system views of DTS and SSIS on a whiteboard, it might be difficult to tell them apart, depending on how you draw them. There are still transforms, tasks, precedence constraints, connections, and variables, and the purpose for the products are closely related. But that's where the similarities end.
There is very little code shared between the two products. The architectures are very different. The scope of Integration Services is largely expanded to be a true enterprise-level Integration and Extract, Transform, and Load (ETL) platform. The data integration and transformation features in Integration Services are hugely improved and the application itself provides a more robust environment for true ETL. In short, anyone reading this should clearly understand that although Integration Services was designed upon ideas developed in DTS, it is a wholly different product.
The Scope of Differences
This section briefly covers some of the more pronounced differences between Integration Services and Data Transformation Services. Many improvements or additional features in IS were not found in DTS. For example, the designer has been vastly improved and there are a large number of new tasks and transforms. But, because this chapter is about migrating existing DTS solutions, the discussion is constrained to those feature differences that are specifically problematic when migrating. Table 3.1 shows the list of the four most problematic feature differences.
Table 3.1. Major Feature Differences Between DTS and SSIS
|
Feature |
DTS Solution |
SSIS Solution |
Description |
|
Data transformation |
Limited transforms and ActiveX script with the pump |
Numerous stock transformations, error outputs, and easy extensibility with custom components in the Data Flow Task |
DTS transforms were limited and ActiveX was slow. ActiveX transformations cannot be migrated. |
|
Control flow looping |
ActiveX scripting using DTS object model |
Built-in Loop container objects with stock enumerators |
DTS had no built-in looping constructs and users were forced to hack the object model with ActiveX scripts. ActiveX looping code 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 traversed the package object model to configure other tasks. SSIS no longer allows tasks access to the object model. |
|
Custom behavior in packages |
The ActiveX Script Task was used extensively to provide not only task behavior, but DTS runtime behavior. |
The Script Task can only be used to provide task functionality and cannot access the object model or other tasks. |
This is a specific instance of a general problem, which is that tasks accessing the object model are difficult to support and upgrade. |
As you can see from Table 3.1, most of the problems arise from the differences between the Pump Task and the Data Flow Task, using ActiveX and allowing tasks to access the object model in promiscuous ways. The Integration Services Data Flow Task is a whole different class of integration and transformation tool, with its own object model and new set of transformations and adapters. The differences are too numerous to list here. ActiveX scripting was a great tool for one-off, simple work. However, because it is interpreted, ActiveX scripting can be quite slow and doesn't scale well. Finally, DTS lets tasks modify the object model of an executing package, which created numerous problems, not the least of which was how to seamlessly migrate packages if the object model changes. Microsoft decided to eliminate task object model access altogether for these and many other similar reasons. Because of these and other differences, you take a one-time migration hit with the expectation that by eliminating the previously mentioned barriers to migration, future upgrades will be much easier.
Good News
Now, for the good news: Microsoft took great care to provide viable and reasonable migration paths for existing DTS packages. You, as the DTS user, have the ultimate control for how and when you migrate to Integration Services. As you'll see in the following sections, you have options for upgrading, migrating, embedding, sharing, calling, and/or running side by side with DTS packages so that you can continue to use your existing investment in DTS while migrating to Integration Services according to your requirements. Also, the upgrade process, no matter how you approach it, is noninvasive and keeps your packages intact.