Upgrading from DTS

As you can see, Microsoft is giving you every incentive to migrate your packages from DTS to SSIS, since there are very few tools to help you manage the packages in DTS. To migrate a DTS package, you can use the DTS Migration Wizard. The wizard is good at migrating packages, but you may be unhappy with the outcome of the package it creates. The success rate for the package migration is generally less than a coin toss for most solutions. This section shows how to use this migration wizard, and then how to handle the package it creates.

The following table shows what we have seen in several migration projects that we’ve been a part of. The table shows the probability of successfully migrating some of the core tasks. Your results may vary, based on the type of work you’re doing inside of DTS.

Open table as spreadsheet

Task	Maps to IS Task	Success Probability
ActiveX Script	ActiveX Script	20 percent
Analysis Services	Analysis Services Processing	0 percent
Bulk Insert	Bulk Insert	80 percent
Data Driven Query	Execute DTS 2000 Package	50 percent
Dynamic Properties	Script Task	0 percent
Execute Package	Execute DTS 2000 Package	90 percent
Execute Process	Execute Process	95 percent
Execute SQL	Execute SQL	95 percent
Send Mail	Send Mail (with SMTP)	90 percent
Transform Data	Data Flow	50 percent
Global Variables	Variables	100 percent

Even though the tasks may migrate, they may not actually work after they are migrated. For example, as you can see in the preceding table, the ActiveX Script Task’s migration success is 30 percent. This is not because it doesn’t migrate. Everything in the preceding table migrates to SSIS. The question is whether the object model works in SSIS. If you’re using anything that refers to the DTS object model, then its execution will fail.

For example, in DTS there was some complex coding you could have written to loop through a directory and execute a package for every file found in the directory. Each time you would execute the package, you would set the connection string dynamically. Because the object model has changed, all of this code will no longer work in SSIS. Instead, you would now use the ForEach Loop with a ForEach File enumerator, and in seconds, you can achieve the same result.

You must also consider what you’re missing out on by migrating. This platform is so different that you’re going to be missing out on some of the restartability features, event handlers, logging, and many other very nice features. Because of this, many people decide to use the upgraded package as a starting point, and then complete the migration by re-architecting the package.

The Upgrade Advisor

The first step of any migration is determining how much work you have in store. The best methodology for doing this is the SQL Server Upgrade Advisor (see Figure 11-5). You can download this tool for free off of the Microsoft web site. Its job is to tell you how much of an effort you have to migrate to the new platform.

Figure 11-5: Upgrade Advisor

You can use the Upgrade Advisor to point to any folder on the operating systems of packages or a SQL Server. It will then scan through all the packages on the server or folder and tell you which issues you’re likely to experience. Anyone pointing to a SQL Server will get very standard errors about the Metadata Repository not being supported, or the DTS Designer components not being installed in SQL Server 2005.

The other messages will be conditional, based on the packages on the instance or folder. You will then be presented with a report that you can reference again with the issues in each package, when you need to resolve them, and how. For example, in Figure 11-5 you can see that the Dynamic Properties Task needs to be replaced after the upgrade of the package.

This tool may need to be updated occasionally. You can do this when you first open the Upgrade Advisor. There, you’ll see an option to check for updates. Updates don’t occur often, however.

The Migration Wizard

To migrate DTS packages quickly to SSIS, you can use the Package Migration Wizard. The wizard is about 40 percent to 50 percent effective, based on your individual situation, as referred to before. If you’re using lots of ActiveX Script Tasks or Dynamic Properties Tasks, for example, it will not be a straightforward conversion. The packages will migrate over, but not be ready to use until you adjust them after the migration. The 40 percent to 50 percent of the packages that it does migrate easily can be used immediately without any re-engineering.

The wizard can be launched either in BIDS or in Management Studio. It will be much simpler in the long run, however, if you always use BIDS as your launching point for the wizard. Doing so will automatically import the package or set of packages into the project that you launched the wizard from. Launching the wizard from Management Studio forces you to immediately deploy your package prior to inspection.

To step through the wizard, you can download two packages from the Wrox web site (www.wrox.com) that are very basic, but demonstrate some functionality issues. The packages are named Simple Load.dts and Simple Load (UCASE).dts. Both packages look like Figure 11-6, with some slight variations to their internal operations. The second package has an ActiveX script on the Transform Data Task to uppercase data as it flows through.

Figure 11-6: Appearance of packages for download

The packages have two variables that make the package somewhat dynamic. The strFileName variable sets the source file name and strServerName sets the target server name. The properties are set to these variables by using the Dynamic Properties Task. After the properties are set with this task, a table is created in the TempDB using the Execute SQL Task, and then finally data is loaded into that table. These packages represent most of the scenarios that we see in the industry, and should encapsulate most of the issues you’re going to experience.

To open the wizard in BIDS, right-click the SSIS Packages tree in the Solution Explorer window and select Migrate DTS 200 Package. You will first be asked which packages you’d like to migrate. You can migrate packages that are stored on a SQL Server instance, or an individual DTS package file. If your packages are stored on the file system, you’ll have to migrate packages one at a time (see Figure 11-7). If you want to pull from a SQL Server instance, specify the instance name. If you want to migrate a package file, simply point the File Name option to the .DTS package file.

Figure 11-7: Migrating packages one at a time in the Package Migration Wizard

When you click Next, you’ll be taken to the Choose Destination Location screen. In this screen, you type in the folder name that you want the newly converted packages to be written to. They’ll also be added to your project, and the files will be physically located in this directory.

After clicking Next, you’ll see a list of DTS packages you’re about to convert (Figure 11-8). If you have multiple packages, check each package you’d like to migrate. You can also select the version that you’d like to migrate for each package from the Creation Date drop-down list box. For the Destination Package option, you can change the name of the SSIS package. In some cases, your DTS package will be using a reserved special character, and you may have to change the name of the package here to enable it to convert.

Figure 11-8: List of DTS packages you’re about to convert

The next screen prompts you to type the name of the log file that you’d like to output the success or failure of each package conversion. The package conversions can take a lot of time to complete. For the small package illustrated here, the conversion should take about 30 seconds, but with larger, more complex packages, the migration could take up to 5 minutes or more. If you’re migrating dozens of packages, it sometimes is very useful to output the status to a log, start the migration at night, and let it migrate all night long.

The next screen shows the migration in progress (see Figure 11-9). If there are any problems, you’ll sometimes see pop-up error messages. At other times, you’ll see the status turn to Failure, and you’ll see the error in the Message column. After you click Close, you’re taken back to the project to see the new SSIS package and clean up the problems.

Figure 11-9: Packages migrating

If you’re following this example, perform the tasks mentioned in the previous section for Simple Load.dts and Simple Load (UCASE).dts. Each of these packages will have its own issues that you’ll resolve later in this chapter. Since you’re migrating two packages, you’ll need to run through the wizard twice.