Migrating to SQL Server 2005 Integration Services
There’s a reason that Microsoft didn’t use the name of Integration Services’ predecessor, Data Transformation Services (DTS), for its new SQL Server 2005 extraction, transformation, and loading (ETL) component. SQL Server Integration Services (SSIS) was a complete code rewrite—Microsoft didn’t bring one line of code over from DTS. With industry demands for faster performance and hardware consolidation to handle ever-increasing data complexity and volume, DTS wasn’t positioned as the long-term solution. So while DTS and SSIS are both ETL tools, architecturally they diverge greatly. Because of this, migration will require some redesign and solution changes so that you can leverage the compelling new SSIS features.
Moving from DTS to SSIS is a migration, involving wizard-driven output along with some manual redesign to complete the process. Some DTS tasks have a straightforward upgrade path to SSIS and are accommodated by the wizard. You might be able to use the wizard to upgrade other tasks depending on their use and design, but some tasks might be more difficult to upgrade or not upgradeable. Here’s what you can expect in upgrading your DTS packages:
• Simple data pumps. Data pumps created by the DTS Wizard or that are simple copy column transformations will be upgradeable most of the time.
• Workflow packages. If your packages are mainly workflow based, with tasks such as the Execute SQL task, they will have a fairly straightforward upgrade path and should also be handled by the wizard in most cases.
• Data pumps with transformations. If your developers used the DTS Designer to build packages that use data pumps with transformations, the wizard will encapsulate the old data pump task. The newly migrated package will invoke the old DTS object model at runtime. Before you upgrade your SQL Server 2005 installation to the next version of SQL Server, you'll have to replace this encapsulated functionality with the new SSIS constructs. Also in this category are Data Driven Query Tasks and Parallel Data Pump Tasks.
• Self-modifying packages. You’ll need to redesign DTS packages that leverage the DTS API to manipulate DTS objects and properties. For example, a common scenario is a looping construct in DTS, designed with scripts that use the API to modify wait states on other package steps. You can redesign this construct by using the built-in For Loop and For-Each Loop Containers in SSIS.
• Scripts tasks. A migrated ActiveX Script Task will run unless it tries to access the DTS runtime package objects. Variable access should use the new SSIS variables without redesign. But in general, you should still reevaluate script tasks against the new SSIS functionality and possibly rewrite them using new tasks.
• OLAP processing tasks. As with Data Transformation Tasks, the wizard will encapsulate these tasks, and they will run in SSIS. After the target server has been upgraded, consider replacing encapsulated functionality with the new SSIS functionality.
• Custom tasks. The wizard will encapsulate these custom tasks, and in many cases, they will run in SSIS. However, the best approach is to make new SSIS custom tasks or transformations.
• Dynamic property tasks. You’ll need to redesign these tasks in SSIS, using Expressions and Configurations to replace the Dynamic Property functionality.
Note that you'll have time to incrementally migrate packages over to SSIS. When you install SQL Server 2005, you have the option to install the runtime files required for DTS packages to execute on SQL Server 2005—without SQL Server 2000 needing to be installed. This makes the side-by-side migration a compelling story, especially in an environment where the DTS packages contain many tasks that require manual migration. SSIS also contains an Execute DTS package object when the runtime files or SQL Server 2000 has been installed on the SSIS server. A side-by-side implementation of SQL Server 2005 SSIS and SQL Server 2000 DTS will give you the greatest flexibility as you approach package migration.
