Professional SQL Server™ 2005 Integration Services

12.1. Migrating DTS 2000 Packages to SSIS

Since SSIS is totally redesigned from the ground up and has a brand-new architecture, you may wonder, what do you do with your existing DTS 2000 packages?

The good news is that Microsoft provides the DTS Migration Wizard, which facilitates this process. The DTS Migration Wizard analyzes your current DTS 2000 package and tries to map all its tasks, components, and workflow constraints to their equivalent parts, where applicable, in SSIS. The bad news is that since SSIS is totally reengineered, it is not possible to migrate all packages that you can create in DTS 2000. The Migration Wizard provides a best-effort attempt. If your package cannot be migrated using the wizard, you will have to upgrade it manually. In fact, for those packages, you probably want to use manual upgrade anyway so that you can fully take advantage of the enhanced functions and capabilities.

In this section, you will see how to use the Migration Wizard to upgrade a sample DTS 2000 package. The sample package will use an ActiveX script (WMI) to query the system's services and their startup mode and to pipe the data captured into a CSV file. That data will then be imported into a table you'll create in the Northwind database.

Here are the steps to create a DTS 2000 package that will be used later in this chapter:

1. Create a table ServiceInfo within Northwind database using the following script:

   Use Northwind create table ServiceInfo (ServiceName varchar(100), StartupMode ...