If you’re like many companies, you probably have numerous SQL Server 2000 DTS packages in your SQL Server environment. The thought of upgrading these packages to SQL Server 2005 SSIS can be overwhelming. These packages likely drive production loads, and you’ve spent years stabilizing them. You don’t want to risk any disruptions. In this article, we will explore your options for upgrading these packages automatically and discuss the components that won’t be upgraded.

The Server and Workstation Upgrade

When you upgrade your SQL Server to SQL Server 2005, the DTS packages are carried over in place and are not upgraded as part of the process. All of your production jobs that call these packages are also carried over without any changes. You can continue to run the 2000 packages since the DTS runtime environment comes over. You’ll be able to find your 2000 packages in the Microsoft SQL Server Management Studio under the Management node, then Legacy -> Data Transformation Services.

The problem arises when you need to modify the package after the upgrade. You’ll need to install the SQL Server 2005 Feature Pack (specifically, the Package Designer for 2000) to make changes to the package. This feature pack is not installed by default to keep the installation size smaller. After you upgrade the last node on your SQL Server, the SQL Server 2000 tools are completely removed. It’s important to note that support for the Metadata Repository has been removed in SQL Server 2005, so those packages will not come over. You will need to save those packages into a different storage area like MSDB or the file structure before upgrading your instance. However, you can still use the SQL Server 2005 tools to connect to SQL Server 2000 databases to manage the server and packages.

Package Upgrade Options

The first step in your upgrade path should be to run the SQL Server 2005 Upgrade Advisor. This tool will report any issues with the DTS packages and identify specific packages that may cause problems during the upgrade. In SQL Server 2005 SSIS packages, you have a new task called Execute SQL Server 2000 Package task. This task allows you to execute the old package within a SQL Server 2005 SSIS package until you have time to upgrade each component. The task has the option to embed the 2000 package into the task itself, so as you move the SSIS package, the DTS package comes with it. This tactic will be used later by the Upgrade Wizard for complex logic.

The best way to upgrade your packages is with the Package Upgrade Wizard. The wizard will leave the old package and calling jobs in place but clone the package with no job. However, the wizard is not perfect. It was developed to perfectly upgrade simple workflows and transforms written with the Import/Export Wizard or similar logic. If you have customized transforms or complex tasks, a small 2000 DTS package that contains that step will be created, and an Execute SQL Server 2000 Package task will be created to call that package. The package will be embedded in the task, and you can modify the 2000 package by clicking Design Package inside that task (it won’t show up in your legacy packages node).

There are some components that will not be compatible with SQL Server 2005. However, just because they’re not compatible doesn’t mean there’s not an upgrade path. Here are some areas of special consideration:

• The Dynamic Properties task has no 2005 equivalent task to port to. A placeholder Script Task will upgrade over but will not have any functionality. Your package will not work as expected until you upgrade the logic using the new Package Configuration option or expressions.
• Analysis Services tasks are wrapped in an Execute 2000 Package task.
• ActiveX Script tasks that extensively use the DTS object model will not be supported. For example, if you used the model to call other packages or loop, this will not port. You will want to implement some of the built-in controls for this, like the For Each Loop container.
• Global Variables are ported to package variables with no issues.
• Data Driven Query tasks are not supported. The upgrade wizard will use a sub-2000 package to make this work.
• Any custom task will be wrapped in a subtask as well.

It’s important to note that long-term support for SQL Server 2000 DTS is not in the cards. It is recommended to spend this release of SQL Server 2005 getting off of DTS because support for DTS is unlikely to exist in the next release.

Upgrading your SQL Server 2000 DTS packages to SQL Server 2005 SSIS may seem like a daunting task, but with the right approach and tools, it can be done smoothly. By following the steps outlined in this article, you can ensure a successful upgrade and take advantage of the new features and capabilities offered by SSIS.

Thank you for reading!