Share via

  • Article

Considerations for Upgrading Data Transformation Services

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.

SQL Server 2008 does not install run-time support for DTS packages. You have to install this run-time support before you can run DTS packages. For information about how to install support for DTS, see How to: Install Support for Data Transformation Services Packages. For more detailed information about support for DTS in SQL Server 2008, see Support for SQL Server 2000 DTS in SQL Server 2008 R2.

Important

Data Transformation Services (DTS) is deprecated. For more information, see Data Transformation Services (DTS).

After upgrading, you can optionally use the DTS Package Migration Wizard to migrate DTS packages to the Integration Services format. During migration, the wizard copies DTS packages and then re-creates them in the Integration Services format. The source packages are left intact and are not modified. For more information, see Migrating Data Transformation Services Packages.

Note

The DTS Package Migration Wizard is available in the following editions of SQL Server: Standard, Enterprise, and Developer.

Before Upgrading Data Transformation Services

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

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

The following procedure describes how to use Setup to upgrade an installation of SQL Server 2000 Data Transformation Services (DTS) to Integration Services.

To upgrade from Data Transformation Services to Integration Services

  • Run SQL Server 2008 Setup, and then select Upgrade from SQL Server 2000 or SQL Server 2005.

    —or—

  • At the command prompt, run setup.exe and specify the /ACTION=upgrade option. For more information about how to install at the command prompt, see the section, "Installation Scripts for Integration Services," in How to: Install SQL Server 2008 R2 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.

For more information about how to upgrade, see How to: Upgrade to SQL Server 2008 R2 (Setup).

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.

Important

Data Transformation Services (DTS) is deprecated. For more information, see Data Transformation Services (DTS).