Expert SQL Server™ 2005 Integration Services

11.3. 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.

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. ...