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

DTS and Integration Services are completely different products. Integration Services is not a version upgrade from DTS. Therefore, there are no software conflicts between DTS and Integration Services. You can do the following procedures:

• Install both DTS and Integration Services on the same server.

• Run both DTS and Integration Services packages on the same server. Even if the SQL Server 2000 tools are not present on the server, you can run DTS packages by using the updated version of the DTS runtime that is installed with Integration Services.

• Save DTS packages in the msdb database of an instance of SQL Server 2000, or an instance that is running SQL Server 2005 or a later version. However, you can save SQL Server 2008 Integration Services packages only on an instance that is running SQL Server 2008.

• Use an optional download of the DTS Designer to modify DTS packages even if the SQL Server 2000 tools are not present on the computer. However, you cannot open or modify DTS packages directly in Business Intelligence Development Studio.

  Note

  For information about how to download the updated version of the DTS Designer, see the section, Installing Design-time Support for SQL Server 2000 DTS Packages.

When you upgrade an existing instance of SQL Server to SQL Server 2008, the system tables (for example, sysdtspackages) that DTS uses in the msdb database remain intact. DTS packages are not automatically migrated. For information about how to migrate packages, see the section, Migrating DTS packages.

Back to the top of this section

After you install the optional run-time support for DTS as described in the section, "Installing run-time support for DTS packages," you can manage DTS packages from SQL Server Management Studio. These DTS packages can be stored in an instance of SQL Server 2000, or imported to and stored in an instance that is running SQL Server 2005 or a later version.

To view DTS packages in Management Studio, first connect to an instance of SQL Server. (Do not connect to an instance of Integration Services.) Then, in the Object Browser under the Management\Legacy\Data Transformation Services node, you will be able to perform the following tasks:

• When you select the Data Transformation Services node in Management Studio, you can view and open packages, import them to SQL Server from structured storage files, or start the DTS Package Migration Wizard.

• When you select an individual DTS package in Management Studio, you can open the package to modify it, migrate it, export it to a structured storage file, or delete it.

  Note

  To open or modify DTS packages, a separate download of the DTS designer components is required. See the section, Installing Design-time Support for SQL Server 2000 DTS Packages. DTS packages cannot be opened or modified directly in BI Development Studio.

The Integration Services database-level roles grant rights on the Integration Services system tables in the msdb database, but not on the DTS system tables, such as sysdtspackages in the msdb database. You have to grant users rights on the DTS system tables before they can perform certain management tasks, such as importing a DTS package in Management Studio. For information about the Integration Services database-level roles, see Using Integration Services Roles.

Back to the top of this section

Although you can run DTS packages and Integration Services packages on the same computer, SQL Server 2008 does not install run-time support for DTS packages. You have to install this run-time support before you can run and manage DTS packages. For information about how to install run-time support for DTS, see How to: Install Support for Data Transformation Services Packages.

The backward compatibility files that provide run-time support for DTS packages also include run-time support for the SQL Server 2000 Analysis Services Processing task and its dependency, Decision Support Objects (DSO). However, the backward compatibility files do not include design-time support for modifying DTS packages that contain the SQL Server 2000 Analysis Services Processing task. You can modify packages that contain this task only when you have SQL Server 2000 Analysis Services installed on the same computer.

The backward compatibility files that provide run-time support for DTS packages do not include support for the SQL Server 2000 Data Mining Prediction Query task.

Back to the top of this section

You can run DTS packages at the command prompt. Integration Services installs the dtsrun.exe utility when it installs DTS run-time support.

Back to the top of this section

To run DTS packages from a SQL Server Agent job, you must set the job step to Operating system (CmdExec) and use the dtsrun utility (dtsrun.exe) to run the package. You cannot set the job step to SQL Server Integration Services Package. For more information, see How to: Run a Package.

Back to top of this section

You can also run DTS packages as part of an Integration Services package. Integration Services provides the Execute DTS 2000 Package task for running DTS packages within an Integration Services package. For more information, see Execute DTS 2000 Package Task.

Back to top of this section

DTS packages cannot be opened or modified directly in Business Intelligence Development Studio. In addition, neither SQL Server 2008, nor SQL Server 2005, installs the DTS package designer for modifying DTS packages. However, you can download and install the DTS package designer, and use it to modify DTS packages. For information about how to install design-time support for DTS packages, see How to: Install Support for Data Transformation Services Packages. After installing this download, you can view and modify DTS packages from Management Studio, or from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio.

For information about how to manage certain DTS application properties that affect the DTS design environment, see Setting DTS Application Properties.

Back to top of this section

SQL Server provides the DTS Package Migration Wizard for migrating DTS packages to the Integration Services package format. When you select Integration Services for installation, Setup also installs the DTS Package Migration Wizard. You can optionally use the DTS Package Migration Wizard to upgrade DTS packages to the Integration Services format. During migration, the wizard copies the DTS packages and then re-creates them in the Integration Services format. The source packages are left intact and are not modified.

Many packages can be fully migrated to Integration Services, especially DTS packages that use tasks, connections, and other objects that have an equivalent in Integration Services. Other packages can be migrated successfully by encapsulating certain tasks or features that cannot be migrated in an Execute DTS 2000 Package task. For more information, see Migrating Data Transformation Services Packages.

Before running the DTS Package Migration Wizard, you might want to install and run Upgrade Advisor to analyze your existing packages for migration issues. For more information, see Using Upgrade Advisor to Prepare for Upgrades.

Back to the top of this section

To understand how the command-line options for the SQL Server 2000 DTS dtsrun utility (dtsrun.exe) map to the options for the Integration Services dtexec utility (dtexec.exe), see Mapping dtsrun Options to dtexec Options.

Back to the top of this section

To load or save data to an ODBC data source, you can use an ADO.NET source or destination and select the .NET Framework Data Provider for ODBC. It is also possible to use the Script component to create an ad hoc ODBC source or destination for use in a single package. For more information, see Creating an ODBC Destination with the Script Component.

Back to the top of this section

SQL Server 2000 Meta Data Services, typically known as the Repository, is a deprecated component. SQL Server 2005 and later versions do not install or use the Repository.

The DTS designer and the dtsrun utility continue to support DTS packages that were saved to the Repository.

Integration Services supports the Repository only in the Upgrade Advisor and the DTS Package Migration Wizard, and only if the Repository redistributable files are installed on the local computer. (These files are normally present when the computer has either SQL Server 2000 or the SQL Server 2000 tools installed.) When the Repository redistributable files are present, the Upgrade Advisor can scan, and the DTS Package Migration Wizard can migrate, DTS packages that were saved to the Repository. When the Repository redistributable files are not present, the Upgrade Advisor and the DTS Package Migration Wizard can only be used on DTS packages that were saved to SQL Server or to structured storage files.

The Integration Services Execute DTS 2000 Package task cannot run a DTS package that was saved to the Repository. However, the SQL Server 2000 DTS runtime can run such a package. Thus, to have an Integration Services package run a DTS package that is stored in the Repository, follow these steps:

1. Make sure that the Repository files are installed on the computer that is running the Integration Services package. To install the Repository files, install SQL Server 2000, the SQL Server 2000 tools, or the Repository redistributable files on the local computer

2. Create a new DTS package to act as a wrapper for the DTS package that is stored in the Repository. In this DTS wrapper package, use an Execute Package task to execute the package that is stored in the Repository. Save this wrapper package to SQL Server or as a structured storage file.

3. Use an Execute DTS 2000 Package task in the Integration Services package to run the new DTS wrapper package.

Back to the top of this section

SQL Server Express does not include Integration Services nor does it support DTS packages. However, you can work with DTS packages on a server that is running SQL Server Express by using one of the following methods:

• To run existing DTS packages on a server that is running SQL Server Express, you need the DTS runtime on the server. Although SQL Server Express does not include the DTS runtime, you can install the DTS runtime on the server by either installing the SQL Server 2000 client tools or the DTS redistributable files.

• To modify existing DTS packages on a server that is running SQL Server Express, you can use the SQL Server 2000 tools. You can also modify the packages remotely in SQL Server Management Studio on a server that is running one of these versions of SQL Server: Workgroup, Standard, Enterprise, or Developer. SQL Server Express does not include SQL Server Management Studio.

• To migrate existing DTS packages to Integration Services, you need the DTS Package Migration Wizard that is installed with Integration Services. SQL Server Express does not include this wizard. To run the wizard, you can use one of these versions of SQL Server: Standard, Enterprise, or Developer.

Back to the top of this section