RDA Blogs

By RDA Senior Software Engineer Joe Toscano

This tip looks at how to use both the SQL Server 2005 Upgrade Advisor and the Package Migration Wizard to migrate your SQL 2000 DTS Packages to SQL Server 2005 Integration Services. Throughout this article SQL Server 2000 DTS will be referred to simply as DTS while SQL Server 2005 SSIS will be referred to as SSIS. We will see how to use the tools first for a very simple DTS package and then for a commonly utilized DTS Looping example. We will see that our simple DTS package migrates to SSIS while our DTS Looping example is another story. This article leverages the wealth of information found at www.sqldts.com and www.sqlis.com by referencing examples found at these sites.

The SQL Server 2005 Upgrade Advisor

Using the compiled knowledge from the product team, internal lab testing, and extensive SQL Server 2005 early adopter experience, Microsoft has developed an essential tool for your upgrade preparation called the Upgrade Advisor. The Upgrade Advisor is a free tool you can download that analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005 – without modifying any data on your servers. The Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describe specific issues and how to resolve them. The Upgrade Advisor analyzes the following components: Database Engine, Analysis Services, Notification Services, Reporting Services and Integration Services. We will focus on migrating DTS packages on a SQL 2000 instance to SSIS. For Upgrade Advisor information regarding download details, system requirements, and usage instructions, please use the following link:
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en

We will use the DTS Migration Wizard to migrate two DTS packages. The first DTS package is a simple traditional data transfer example while the second DTS package performs looping using Active X scripts and calls to the DTS 2000 Object Model.

DTS Package 1: Our Traditional DTS Data Transfer / Validation Example

I’ve designed many DTS Packages that extract and consolidate data from multiple sources and place this data into a SQL Server staging table. Since SQL Server is the ‘playground’ that I’m extremely comfortable with, once data is there I can validate, de-dup, consolidate, transform, resolve lookup values, handle slowly changing dimensions and so on. Under DTS, I typically employ stored procedures to accomplish many of these tasks. (Since SSIS supports a much larger number of features and much larger number of pre-baked tasks, most of this work can now be accomplished ‘in-line’ without external calls to stored procedures.) Our first Traditional DTS package does not utilize custom tasks, data driven query tasks, dynamic properties, or Active X scripts. It also does not make calls to the DTS Object Model. What should your strategy be if you need to migrate a package like this? We will address this in the sections that follow. Below is our Traditional DTS Data transfer package:

DTS Package 2: Our DTS Looping example

A common ETL task we may all have encountered is looping through a source directory to process files and moving the files to an archived directory. Since this functionality is not built in to DTS, it must be implemented using Active X scripts. These scripts utilize the File System Object to move processed files from an input directory to an archived directory. Further, these scripts dynamically change our data source to the file just discovered in a source directory. The looping is actually performed using the DTS Object Model to disable or enable steps depending on whether we’ve processed all of the source files in a source directory or if there are more files to process. Although our sample package has been modified slightly, the original DTS Looping Package is fully documented and can be downloaded from the following site:
http://www.sqldts.com/default.aspx?246

It’s quite possible that many folks who’ve had to loop in DTS used this example found in www.sqldts.com as their starting point, just as I did. What issues will you encounter if you need to migrate a package like this? We will address this question in the sections that follow. Below is our DTS Looping Package:

Invoking the Upgrade Advisor

The SQL Server 2005 is built on a rules-based engine. When you execute the tool, a simple wizard prompts you to select components on a local or remote server. Based on your selection, the wizard prompts you to identify details about each component, such as a specific DTS Package to analyze. I am providing the Advisor’s screen snapshots below. (For the sake of brevity, some screens were omitted.)

What SQL Server component(s) would you like to analyze ?

Are your DTS packages in SQL Server or saved as a file ?

The Upgrade Advisor’s Findings

Notice that the Upgrade Advisor Report that is generated categorizes the issues you may encounter as red potential show-stoppers or yellow informational warnings. (you can drill down on each item to display more detailed information.) Further, the Advisor tells us whether the issue needs to be addressed before or after you migrate. Let’s dissect each of the migration issues that have been raised:

Meta Data Services In our report we are first told that you can no longer save SQL Server packages using Meta Data Services. The packages should be moved to SQL Server storage (in the msdb database) or to structured storage files before you upgrade. (We stored our DTS Packages in SQL Server’s msdb database.)
SQL Server 2000 DTS Designer Components This issue is raised in case you want to modify your newly migrated Integration Services packages that may contain SQL Server 2000 DTS Legacy components. (Not all DTS components map to SSIS components.) What if by upgrading or uninstalling the last instance of SQL Server 2000 you’ve lost your 2000 DTS Development Environment? You can retain or restore these components by installing the special Web download “SQL Server 2000 DTS Designer Components” from the Microsoft Download Center before or after you upgrade or uninstall SQL Server 2000.
Active X Script Code / DTS Object Model The upgrade advisor noticed that one or more packages contains Active X Script code that accesses the DTS Object Model. The SQL Server 2005 Integration Services (SSIS) Package Migration Wizard does not migrate Active X Script code that accesses the DTS object model . After migration, we must manually modify the resulting SSIS package to restore former package behavior.

Traditional DTS Package Migration Strategy

Based on the reports generated by the Upgrade Wizard, I chose to migrate the Traditional DTS Package using the Package Migration Wizard. Even though this package does migrate and then operate as an SSIS package under 2005, there are several issues with the final product that have been addressed below.

Step 1: Invoking The Package Migration Wizard

The Package Migration Wizard can be launched from multiple places. I chose to invoke the Migration Wizard from the Business Intelligence Development Studio referred to as BIDS throughout the remainder of this document. Launching the wizard from BIDS allows one to migrate the DTS package to an SSIS package file. This file can then be opened in BIDS where it can be tested, re-engineered if necessary, and finally deployed. I launched the Migration Wizard using Project -> Migrate DTS 2000 Package menu options. In the first menu I am asked to provide the SQL 2000 Instance that houses the DTS package(s) I wish to migrate. Below are the screen snapshots of the Migration Wizard:

What Instance contains the 2000 DTS Package(s) we want to migrate ?

Where will the newly migrated SSIS (.dtsx) file(s) reside ?

Choose the specific DTS package(s) to migrate

Step 2: Re-engineering the Traditional SSIS Package

Even though this DTS package did migrate, we are not taking advantage of SSIS features and not following the SSIS in-line Design Philosophy. Since SSIS supports such a large number of pre-baked transformations, why not make every attempt to avoid external calls to stored procedures? For example, the package can be redesigned to use the Lookup Task instead of calling a stored procedure to do this. Also, the Slowly Changing Dimension Wizard can be used instead of calling a Stored Procedure.

The Looping DTS Package Migration Strategy

This DTS Package should be re-engineered since there are Active X Scripts and calls to the DTS Object Model. Since SSIS supports industrial strength programming concepts we can use containers to perform the looping and dynamically change the source file to process the newly discovered file. Also, we can use the File System Task to move processed files from a source directory to an archived directory. It turns out there is an excellent example of an SSIS package that does exactly this located at www.sqlis.com. This example entitled ‘Looping over files with a ForEach loop’ can be found in the following link: http://www.sqlis.com/default.aspx?55

Conclusions

The Upgrade Advisor can be used to point out critical migration issues you may encounter with your DTS Packages. It can be used to help you gauge the level of effort required for your DTS Package migration. In some cases you may choose to perform the migration but leave some of the 2000 DTS components intact. Perhaps you then can start working on a new version that replaces these legacy components with an SSIS-only version that fully utilizes SSIS’s muscles. Keep in mind that just because the Advisor may give your DTS Package a thumbs-up you still may have post-migration work to perform to take advantage of SSIS features.

Review the features of SSIS carefully. Knowledge of these features is vital to successfully and optimally re-engineering your DTS packages.