Coordinating a DTS-to-SSIS Migration

How many SQL Server 2000 DTS packages are you responsible for? Perhaps 10? Maybe 50? When I asked this question at a conference last year, a few attendees shocked me by telling me that their environments boasted more than 2000 DTS packages! Was it irresponsible of me to wish them good luck in their migrations? Perhaps. But if you have several hundred DTS packages, you should know that you’re not alone. DTS is well known for its simplicity and usefulness, and in fact, many organizations adopted DTS for its ability to quickly and simply generate data processing.

Whether you have 10 DTS packages or several hundred, you’re probably preparing for an inevitable migration to SQL Server 2005 Integration Services (SSIS). However, in your testing and research, the pain points have become apparent: The product’s architecture differs from that of SQL Server 2000 DTS—making the migration more complex than a straight upgrade—and even after you migrate a couple packages, you might see only a little performance gain. And if you’re looking at dozens or hundreds of packages, you can expect several sleepless nights.

Your First Step
A good starting framework for a DTS-to-SSIS migration strategy includes first getting your DTS packages to SQL Server 2005. Don’t let your DTS migration hold up your relational engine upgrade to SQL Server 2005.

1. Move your DTS packages to your SQL 2005 environment (through the setup.exe SQL upgrade or manually through Management Studio), and continue to run them as DTS packages.
2. Use SSIS to build any new packages you need.
3. Use the DTS Migration Wizard as a starting point for strategic packages that can take advantage of SSIS features or for packages that have trouble during the migration.
4. Plan for a rolling strategy to rework all packages, leveraging the complete SSIS feature set in the redesign.

There’s a light at the end of the tunnel—a bright one. And getting there might not be as difficult as you think. In fact, now that SQL Server 2005 is more than a year old, more resources and knowledge are available to help you through the process. (For a helpful primer, see “Step Up to SQL Server 2005,” InstantDoc ID 47749, as well as this article’s sidebar, “Your First Step.” ) But don’t settle for merely getting your packages migrated to SSIS. Rather, prime yourself for taking full advantage of SSIS’s improved features and functionality.

Migration Resources
When you’re ready to take on your DTS-to-SSIS migration, you’ll need to have at your disposal the tools and resources you need to make informed decisions and perform a clean migration. One such tool is the Upgrade Advisor, which can analyze your existing DTS packages and report about concerns that you must address before and after your migration. The most recent version of Upgrade Advisor is downloadable with the “Feature Pack for Microsoft SQL Server 2005 - April 2006” (http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aab4bd-4705-aa0a-b477ba72a9cb &DisplayLang=en).Even more valuable is the “SQL Server 2005 Upgrade Technical Reference Guide” (http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en), which covers the entire SQL Server platform upgrade in 350 pages, with 38 pages dedicated to DTS package migration to SSIS—invaluable to understanding the nuances of the upgrade. Both of these resources cover the basics of using SQL Server’s built-in DTS Migration Wizard to convert DTS packages to SSIS. To invoke the DTS Migration Wizard, you right-click the SSIS Packages folder in Business Intelligence Development Studio, as Figure 1 shows. (To prepare your packages for the DTS Migration Wizard, see the sidebar “Preparing for the Wizard.”)

One of the biggest challenges involved with migrating DTS packages to SSIS arises when the DTS Migration Wizard can’t convert a DTS component, such as a data pump that does more than copy column operations. In some cases, the converted SSIS package will retain a portion of the original DTS package, embedded in an Execute DTS Package task in SSIS. Other tasks, such as the Dynamic Property Task and some of the logic embedded in an ActiveX task, will need to be rewritten with SSIS functionality. Each of the migration nuances will be identified by the Upgrade Advisor tool or discussed in the upgrade planning and preparation resources.

Beyond just the migration resources that SQL Server 2005 provides in the DTS Migration Wizard and Upgrade Advisor, the software also includes support for DTS packages. For example, the \Management\Legacy folder in SQL Server 2005 Management Studio (SSMS) includes a container for DTS 2000 packages, as you can see in Figure 2. These packages might have been included with the SQL Server relational engine upgrade; you can also load DTS packages after the upgrade into a SQL Server 2005 instance.

With the DTS design tools installed for SSMS (available in the “Feature Pack for Microsoft SQL Server 2005 - April 2006” referenced earlier), you can modify DTS packages through SSMS and execute them on the server. Also, using the Execute DTS 2000 Package Task included in the SSIS Control Flow, you can run DTS packages alongside SSIS packages, so you can move forward with SSIS packages while managing your migration incrementally.