Considerations for Upgrading Data Transformation Services

SQL Server Setup

If SQL Server 2000 Data Transformation Services (DTS) is currently installed on your computer, you can upgrade from Data Transformation Services to SQL Server 2008 Integration Services without any software conflicts. There are no software conflicts because Integration Services is a completely new product and not a version upgrade from Data Transformation Services.

Note:
If you want to upgrade from SQL Server 2005 Integration Services to SQL Server 2008 Integration Services, see Considerations for Upgrading Integration Services.

Before Upgrading Data Transformation Services

Before upgrading Data Transformation Services to Integration Services, we recommend that you review Support for Data Transformation Services (DTS) in SQL Server 2008.

We also recommended that you install and run Upgrade Advisor before you perform an upgrade. For more information, see Using Upgrade Advisor to Prepare for Upgrades.

Upgrading Data Transformation Services

You can upgrade an existing instance of SQL Server 2000 by using one of the following methods:

  • Run SQL Server 2008 Setup and select the option to Upgrade from SQL Server 2000 or SQL Server 2005.
  • Run setup.exe at the command prompt and specify the /ACTION=upgrade option. For more information, see the section, "Installation Scripts for Integration Services," in How to: Install SQL Server 2008 from the Command Prompt.

It is not possible to use SQL Server 2008 Setup to upgrade directly from SQL Server 6.5 or 7.0 to SQL Server 2008.

When you upgrade, you can upgrade both DTS and the Database Engine, just upgrade DTS, or just upgrade the Database Engine. If you upgrade only DTS or only the Database Engine, there are some limitations:

  • If you upgrade only DTS, SQL Server 2008 Integration Services is fully functional. However, Integration Services will only be able to store packages in the file system, unless an instance of the SQL Server 2008 Database Engine is available on another computer. If an instance of the SQL Server 2008 Database Engine is available, Integration Services will be able to store packages in that instance.
  • If you upgrade only the Database Engine, DTS remains functional. However, you will not have the functionality of Integration Services.

Upgrading Both Data Transformation Services and the Database Engine to SQL Server 2008

This section describes the effects of performing an upgrade that has the following criteria:

  • You upgrade both DTS and an instance of the Database Engine to SQL Server 2008.
  • Both DTS and the instance of the Database Engine to be upgraded are on the same computer.

What the Upgrade Process Does

The upgrade process does the following tasks:

  • Upgrades the instance of the Database Engine to SQL Server 2008.
  • Installs SQL Server 2008 Integration Services.
  • Installs updated run-time support for DTS packages. This support includes the DTS runtime and DTS package enumeration in SQL Server Management Studio. DTS packages can continue to run alongside Integration Services packages on the same computer. For more information, see How to: Install Support for Data Transformation Services Packages.
  • Leaves existing DTS packages intact in the existing DTS system tables in the msdb database.

What the Upgrade Process Does Not Do

The upgrade process does not do the following tasks:

What You Can Do After Upgrading

After the upgrade process finishes, you can do the following task:

After installing the optional run-time support for DTS, you can do the following tasks:

  • To view DTS packages under the Management\Legacy\Data Transformation Services node in SQL Server Management Studio, connect to the upgraded instance of the Database Engine. (When you connect to the Integration Services service, you can only view Integration Services packages.)
  • To run DTS packages within an Integration Services package, use the Execute DTS 2000 Package task. For more information, see Execute DTS 2000 Package Task.
  • To run DTS packages at the command prompt, use the dtsrun.exe utility.
  • To run DTS packages from SQL Server Agent jobs, call the dtsrun.exe utility from a job step.

For more information about how to run packages, see Running Data Transformation Services (DTS) Packages.

See Also

Concepts

Support for Data Transformation Services (DTS) in SQL Server 2008

Other Resources

Interoperability and Coexistence (Integration Services)