Support for Data Transformation Services (DTS) in SQL Server 2008
Although SQL Server 2000 Data Transformation Services (DTS) is deprecated, SQL Server 2008 provides optional 32-bit management, run-time, and design-time support for packages that were created by using the DTS tools and object model. (For more information about the deprecation of DTS, see Data Transformation Services (DTS).)
Important
The features described in this topic, including run-time support for DTS packages, require the manual installation of additional, optional components that are not available during setup. For more information, see the sections, "Installing run-time support for DTS packages " and "Installing design-time support for DTS packages," later in this topic.
Be aware that not all DTS functionality is supported in all circumstances. The following sections describe exactly what DTS functionality is supported in SQL Server 2008.
Limited DTS Functionality on 64-bit Operating Systems
SQL Server 2008 does not include support for DTS in the following circumstances:
There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. For more information, see How to: Install Support for Data Transformation Services Packages.
There is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems.
Supported DTS Functionality
SQL Server 2008 includes support for the following DTS features:
The DTS runtime, the object model that it exposes, and the dtsrun.exe command prompt utility.
The Execute DTS 2000 Package task, for executing DTS packages within Integration Services packages.
The ActiveX Script task, for backward compatibility only.
The DTS Package Migration Wizard, for migrating DTS packages to the Integration Services package format.
The Upgrade Advisor rules for DTS packages, for identifying potential issues that may be encountered when migrating packages.
Important
The Execute DTS 2000 Package task, the ActiveX Script task, and the DTS Package Migration Wizard are installed when you select Integration Services for installation. However, these components are not fully functional without the manual installation of additional, optional components that are not available during Setup. For more information, see the section, "Installing run-time support for DTS packages," later in this topic.
The remainder of this topic discusses in more detail the DTS functionality that SQL Server 2008 supports. Use the following links to quickly connect you to this content:
General information
General compatibility between DTS and SSIS
Managing DTS packages
Designing and running DTS packages
Installing run-time support for DTS packages
Running DTS packages at the command prompt
Running DTS packages from SQL Server Agent jobs
Running DTS packages from Integration Services packages
Installing design-time support for DTS packages
Migrating DTS packages
Migrating DTS packages
Migrating dtsrun commands to dtexec commands
Limited support
Support for ODBC data sources
Support for Repository packages
Support for DTS Packages in SQL Server Express
General Compatibility between DTS and Integration Services
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.
.gif "Arrow icon used with Back to Top link")
Back to the top of this section
Managing DTS Packages
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.
Important
For important information about how to secure DTS packages, see Securing DTS Packages Stored in SQL Server.
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
Installing Run-time Support for DTS Packages
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
Running DTS Packages at the Command Prompt
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
Running DTS Packages from SQL Server Agent Jobs
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
Running DTS Packages from Integration Services Packages
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
Installing Design-time Support for DTS Packages
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
Migrating DTS Packages
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.
Note
The DTS Package Migration Wizard is available in the following editions of SQL Server: Standard, Enterprise, and Developer.
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.
Note
Upgrade Advisor cannot analyze, and the DTS Package Migration Wizard cannot migrate, DTS packages that are saved in the msdb database of an instance of SQL Server 7.0. However, the Upgrade Advisor can analyze, and the DTS Package Migration Wizard can migrate, SQL Server 7.0 DTS packages that are saved as structured storage files. Also, the Upgrade Advisor can analyze, and the DTS Package Migration Wizard can migrate, SQL Server 7.0 DTS packages that are saved in the msdb database of an instance of SQL Server 7.0 that has been upgraded to SQL Server 2005 or a later version.
Back to the top of this section
Migrating Command Lines for Executing Packages
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
Support for ODBC Data Sources
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
Support for Meta Data Services (Repository) Packages
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:
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
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.
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
Support for DTS Packages in SQL Server Express
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