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 varchar(10)) 

2. Create a new package called GetServiceInfo. Create a connection to the Northwind database within the package.

3. Create an Execute SQL task to clean up the ServiceInfo table:

    truncate table ServiceInfo 

4. Create an ActiveX Script task that uses WMI to query the system's service info. Services present on the system and their startup mode will be retrieved and piped into a CSV file. Note that the folder in this example is C:\SSIS. If you use a different folder for your testing, you need to change that accordingly. Please see Chapter 16 if you want to learn more about WMI:

    '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Option Explicit Function Main() Dim strLocalFolderName, strOutputFileName, strComputer Dim objFSO, objWMIService, objMyFile, colServiceList, objService strLocalFolderName = "c:\SSIS" strOutputFileName = strLocalFolderName & "\ServiceAndStartMode.csv" strComputer = "." Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") Set colServiceList = objWMIService.ExecQuery _ ("Select * from Win32_Service") Set objFSO = CreateObject("Scripting.FileSystemObject") If (objFSO.FileExists(strOutputFileName)) Then     objFSO.DeleteFile (strOutputFileName) End If Set objMyFile = objFSO.CreateTextFile(strOutputFileName, True) objMyFile.WriteLine ("ServiceName,StartMode") For Each objService In colServiceList     objMyFile.WriteLine (objService.DisplayName & "," & objService.StartMode) Next objMyFile.Close Set objFSO = Nothing Set objWMIService = Nothing Set objMyFile = Nothing Set colServiceList = Nothing Set objService = Nothing Main = DTSTaskExecResult_Success End Function 

5. Create a Transform Data task to import data from the CSV file generated from the preceding step into the ServiceInfo table. Use the text file ServiceAndStartMode.csv as Source and the ServiceInfo table as Destination.

6. Create the appropriate workflow process.

Figure 12-1 shows what this DTS 2000 package should look like.

Figure 12-1

Your sample package is now set up and ready to go.