DTS (or Data Transformation Services) for all reasonable purposes is out in SQL Server 2005 and replaced by a brand-new ETL engine called SQL Server Integration Services (SSIS). As a DBA you're wondering what to do with your DTS packages.
This tip looks at the most reasonable solution -- one that will allow you to take advantage of the fully-loaded SSIS. We'll look at how to use both the SQL Server 2005 Upgrade Advisor and the Package Migration Wizard to migrate your SQL 2000 DTS Packages to SSIS. The Upgrade Advisor can point out potential migration issues while the Package Migration Wizard will map tasks, components and workflow constraints to their equivalent in SSIS.
We'll first see how to use tools for a very simple DTS package and then for a commonly utilized DTS Looping example. Our simple DTS package migrates to SSIS while our DTS Looping example is another story. This document leverages the wealth of information found at sqldts.com and sqlis.com by referencing examples found at these sites.
TABLE OF CONTENTS
[IMAGE] Tools for a successful migration
[IMAGE] Traditional DTS transfer vs. looping
[IMAGE] Invoking the Upgrade Advisor
[IMAGE] Traditional DTS Package Migration Strategy
[IMAGE][IMAGE] Tools for a successful migration[IMAGE] Return to Table of Contents
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 upgra...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
de 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 features and configuration changes that might affect your upgrade, and it provides links to documentation describing each identified issue and how to resolve it. The Upgrade Advisor will analyze the following components: Database Engine, Analysis Services, Notification Services, Reporting Services and Integration Services. We'll focus on migrating DTS packages on a SQL Server 2000 instance to SSIS. Get more on Upgrade Advisor including information on download details, system requirements and instructions.
The Package Migration Wizard
The Microsoft provided DTS Migration Wizard can be used to migrate DTS packages that were created using the SQL Server 2000 tool. It can help provide a best-effort migration attempt by mapping tasks, components and workflow constraints to their equivalent part in SSIS. The challenge here is that since SSIS has been totally re-engineered not all of the 2000 DTS components map to SSIS features. In fact there are documented migration issues in Books Online.
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.
[IMAGE][IMAGE] Traditional DTS transfer vs. looping[IMAGE] Return to Table of Contents
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' 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 coded stored procedures to accomplish many of these tasks. Since SSIS supports a much larger number of features and pre-baked tasks, most of this work can now be done 'in-line' without external calls to stored procedures. Our first traditional DTS package does not utilize custom tasks, data driven query tasks, dynamic properties, Active X scripts or make calls to the DTS Object Model. What should your strategy be if you need to migrate a package fitting such a description? We'll address that in the following sections. Below is our traditional DTS data transfer package:
[IMAGE]
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 into DTS, it must be implemented using ActiveX 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. But this depends on whether we've processed all of the source files in a source directory or 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.
It's quite possible many folks who've had to loop in DTS used this example found at as their starting point just as I did. In the next section, we'll address issues you'll encounter when needing to migrate a package like that. Below is our DTS Looping Package:
[IMAGE]
[IMAGE][IMAGE] Invoking the Upgrade Advisor[IMAGE] Return to Table of Contents
Invoking the Upgrade Advisor
The SQL Server 2005 Upgrade Advisor 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).
[IMAGE]
What SQL Server component(s) would you like to analyze?
[IMAGE]
Are your DTS packages in SQL Server or saved as a file?
[IMAGE]
The Upgrade Advisor's Findings
Notice the generated Upgrade Advisor Report categorizes 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 indicates whether the issue needs to be addressed before or after your migration. Let's dissect each of the migration issues that have been raised:
Meta Data Services
In our report we are first told that we 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. Remember: not all DTS components map to SSIS components. What happens 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 one or more packages contains Active X Script code that access the DTS Object Model. The SQL Server 2005 Integration Services (SSIS) Package Migration Wizard does not migrate ActiveX Script code that accesses the DTS object model . After migration, we must manually modify the resulting SSIS package to restore former package behavior.
[IMAGE][IMAGE] Traditional DTS Package Migration Strategy[IMAGE] Return to Table of Contents
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 a SSIS package under 2005, there are several issues with the final product 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 a 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 housing the DTS package(s) I wish to migrate. Below are the screen snapshots of the Migration Wizard:
[IMAGE]
What Instance contains the 2000 DTS Package(s) we want to migrate?
[IMAGE]
Where will the newly migrated SSIS (.dtsx) file(s) reside?
[IMAGE]
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 to the newly discovered file. We can also use the File System Task to move processed files from a source directory to an archived directory. You can see an excellent example of an SSSI package by going to 'Looping over files with a ForEach loop.'
Conclusion
The Upgrade Advisor can point out critical migration issues you may encounter with your DTS Packages and help you gauge the effort level required for your migration. In some cases you may choose to perform the migration but leave some of the 2000 DTS components intact. Perhaps you can then start working on a new version that replaces these legacy components with an SSIS-only version to fully utilize SSIS's muscles. Keep in mind that just because the Advisor gives your DTS Package a thumbs-up you may still have post-migration work to perform in order to take advantage of SSIS features.
Review the features of SSIS carefully. Knowledge of these features is vital in successfully and optimally re-engineering your DTS packages.
About the Author: Joe Toscano of RDA is a SQL Server database consultant, instructor and speaker based near Philadelphia, Pa. He has worked extensively with SQL Server since version 6.5, as well as Sybase and Informix prior to that. Joe's areas of expertise include Data Transformation Services (DTS), Integration Services (SSIS), Transact-SQL (T-SQL) programming, data warehousing, performance tuning, replication, high availability (mirroring), security, disaster recovery, upgrading, migration projects and lights-out DBA tasks.
More on SearchSQLServer.com
Tip: SQL Server Integration Services: Simplify database maintenance
Tip: SQL Server 2005 upgrade hurdles
Book Excerpt: Programming and Extending SSIS: Chapter 14 of Professional SQL Server 2005 Integration Services
