Upgrading DTS packages to SQL Server Integration Services

Determining how you will upgrade your DTS packages to SQL Server Integration Services (SSIS) is the first step in creating a DTS-to-SSIS migration strategy.

To start the upgrade, run the SQL Server 2005 Upgrade Advisor, which has an option specifically for DTS. Since everything has changed between DTS and SSIS, the upgrade will not be easy, but that shouldn’t concern you as long as you understand the process and what you’ll need to do going forward.

What happens during the upgrade?

When you upgrade an existing SQL Server instance, DTS runtime remains in place. Your DTS packages are not affected and local server packages remain stored in the msdb.dbo.sysdtspackages table. Structured storage files, or packages saved to DTS files, are also unchanged.

If you use Meta Data Services, previously called the Repository in SQL Server 7.0, you may encounter some problems. Meta Data Services is not supported by SSIS, which is not surprising considering that SQL Server 2000 Service Pack 3 removed Meta Data Services as a storage location. This is not a significant change, however, since the existing DTS object model and the DTSRUN command-line tool continue to support Meta Data Services.

The most important thing to remember about the DTS-to-SSIS upgrade is that after it is complete, your packages will continue to run. Scheduled jobs will still use DTSRUN and call the same packages from the same locations as they did prior to the upgrade.

When making the upgrade, you will need to determine how you will manage these legacy DTS packages in the future. The DTS runtime will continue to be available, and will have been updated to enable DTS packages to connect SQL Server 2005 data sources. But the DTS designer components are removed along with SQL Server 2000 Enterprise Manager.

You cannot connect to a SQL Server 2005 instance using the SQL Server 2000 Enterprise Manager. But SQL Server Management Studio, which is the SQL Server 2005 replacement for Enterprise Manager, supports DTS packages.

Downloading designer tools

In the Object Explorer window, under the Management Legacy nodes, you will find Data Transformation Services. This is the equivalent of local packages, and is the same table as mentioned above. You can import packages and start the DTS-to-SSIS migration wizard from there, but to do any editing work or manage packages you must download and install the Microsoft SQL Server 2005 DTS Designer Components. These can be found at the Microsoft download center as part of the feature pack for SQL Server 2005.

With the full DTS Designer, you can now create or edit DTS packages as you have done in the past. SQL Server Management Studio does not support Meta Data Services, so you will not be able to enumerate or edit packages stored there. As previously discussed, Meta Data Services will continue to be available through the object model and DTSRUN, but there is no support from management tools.

If this is problematic for you, move your Meta Data Services packages into the Local Packages store or files for easy maintenance. Without tools, DTSRUN can be used to save packages to a file: DTSRUN /Sserver /E /Npackagename /FX:\packagename.dts. Execute Package Tasks will need updating to reference the new storage location you choose.

On machines with multiple instances of SQL Server 2000, upgrading one instance to SQL Server 2005 leaves the remaining instances unchanged. The SQL Server 2000 tools, including Enterprise Manager, are unchanged as well. Since the original designer supplied with SQL Server 2000 will be intact, there is no need to install the Microsoft SQL Server 2005 DTS Designer Components. This ensures that you retain full management capability of the SQL Server 2000 instance, which will be shared with SQL Server Management Studio.

An important thing to remember is that you do not have to tackle high-risk upgrades or migration immediately. Eventually you will want to migrate DTS packages to integration services packages. The migration wizard would seem like the obvious route, but because of fundamental differences between the two architectures, it is not a trivial process.

Making the move

Let’s look at what can happen if you use the Package Migration tool to move your DTS 2000 packages to SSIS 2005. Keep in mind that a complex DTS 2000 package that involves a lot of glue code and performs some interesting things with the workflow cannot be plugged into the wizard with the expectation that it will come out the other end looking like an SSIS equivalent. It can’t. What you get instead is a best-effort attempt at the upgrade.

As an example, we’ll upgrade a package that can be found on our web site at http://www.sqldts.com/default.aspx?246. It covers object model manipulation, global variables and data pump tasks, all of which present certain challenges to the migration wizard.

When we upgraded the package, our first impression was that the results looked good. Upon further inspection, that was not the case. Let’s have a look at that and discuss how we migrated the package using the wizard. 

Here are a few ways to locate the wizard:

• On my PC I navigated to E:\Program Files\Microsoft SQL Server\90\DTS\Binn and located the executable DTSMigrationWizard.exe.
• From within an SSIS project, right click on the SSIS packages folder and click on Migrate DTS 2000 Package.
• In SSMS, expand the database server, expand management, and then expand legacy. Right click on the data transformation folder.

When you launch the wizard, you will see a splash screen. Click next to retrieve the DTS package. In our example, we chose a structured storage file, but it could easily have been a SQL Server package. Click next to specify to what location you want to save the migrated package. Again, you can store the package to SQL Server, but we have chosen to go to the file system.

The result

The storage file could contain more than one package, so on the next screen we select the packages we want to migrate. In our example we only have one package, which is password protected. On the next screen we specify the password status. Notice that the package moved into the box of authenticated packages. It is useful to have a log of what happens with your package, and the next screen enables you to specify a log location.

The last screen in the wizard presents a checklist of what it will do before attempting the upgrade. Once the package has been migrated, we can look at it in the Business Intelligence Development Studio. As mentioned earlier, the conversion looked good on first inspection, but the package would fail every time because the active script tasks make constant reference to step names, and these cannot be resolved when we execute the package. The error we see is:

The global variables in the DTS 2000 package have been successfully migrated, as has the separation of the connection manager from the source adapter. This package could be fixed, but there is another glaring error. SSIS introduces the Foreach Enumerator, which is easier to use than Active X Script, and is more robust and easier to debug. If we wanted to migrate this package, it would be best to rewrite it from scratch rather than using the wizard.

There is another well-used strategy of the migration wizard that is not shown in our example: Preserve the DTS package task and encapsulate it in a new special package. This package will contain tasks that cannot be migrated at all, and are then embedded within an Execute DTS 2000 Package Task, which is added to the new SSIS package. This keeps the DTS elements working and within DTS, and calls them as required from the new SSIS package.

Conclusion

The migration wizard will try to use an SSIS equivalent to a DTS task. Sometimes this works and sometimes it does not, as illustrated in our example. The Active X Script task in DTS is the same in SSIS. What is not the same is the way you manipulate the object model from within. The transform data task and the data flow task are similar in DTS and SSIS, and the wizard does an excellent job of migrating that structure along with connection managers.

There are tasks that have no equivalent. One that comes to mind is the dynamic properties task in DTS. It does not exist in SSIS, and the equivalent in SSIS would be to use configurations or expressions, which are now part of the engine and package architecture as opposed to specific tasks.

You may decide to leave DTS running alongside SSIS. We covered the runtime and management sides of DTS within SQL Server 2005, so if the migration wizard is not working for you or you lack test resources to complete such a project, this would be a viable option.

Darren Green is the founder of SQLDTS.com. After a brief experience with SQL Server 6.5 and BCP, he moved onto version 7.0 and the joys of DTS. He is a SQL Server MVP and can frequently be found in the Microsoft public newsgroups. In his spare time he is lead DBA for one of the top independent financial advisor portals in the U.K.

Allan Mitchell began working with SQL Server version 6.5. He is an MCSE on Windows 2000, an MCDBA and a Microsoft SQL Server MVP, and consults on DTS for an array of clients in the U.S. and Europe. He is the co-author of <A href=”http://www.sqldts.com/?3001>Real-World SQL-DMO for SQL Serverand has been published in SQL Server Professional Magazine.