SQL Server 2005 Integration Services Backward Compatibility

SQL Server Setup

Updated: 17 July 2006

SQL Server 2005 Integration Services (SSIS) provides management, run-time, and design-time support for Data Transformation Services (DTS) packages that were created by using the SQL Server 2000 tools and object model.

General Compatibility between DTS and SSIS

General Compatibility between DTS and SSIS

Because Integration Services is an entirely new product, and not a version upgrade, you will not encounter software conflicts between DTS and SSIS.

  • You can install both DTS and SSIS on the same server.
  • You can run both DTS and SSIS packages on the same server when both products are installed. You can run DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS runtime that is installed with Integration Services.
  • You can save DTS packages in the MSDB database of a SQL Server 2000 or SQL Server 2005 instance. You can save SSIS packages only on a SQL Server 2005 instance.
  • You can edit DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS Designer that is available for download. You cannot edit DTS packages in Business Intelligence Development Studio. You can edit SSIS packages only in BI Development Studio.

Managing DTS Packages

You can manage DTS packages from within SQL Server Management Studio, whether they are stored in an instance of SQL Server 2000 or they have been imported to an instance SQL Server 2005. To view DTS packages in Management Studio, connect to an instance of SQL Server and look in the Object Browser under the Management\Legacy\Data Transformation Services node.

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

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

Note:
To edit DTS packages, a separate download of the DTS designer components is required. See Installing Design-time Support for SQL Server 2000 DTS Packages. DTS packages cannot be opened or edited in BI Development Studio.

Installing Run-time Support for SQL Server 2000 DTS Packages

You can run DTS packages and SSIS packages on the same computer.

When you select Integration Services for installation, Setup also installs support for DTS packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Support in the runtime is enhanced to enable DTS packages to access SQL Server 2005 data sources.

For more information, see How to: Ensure Support for Data Transformation Services Packages.

Note:
Although support for SQL Server 2000 Data Transformation Services is installed automatically when you select Integration Services for installation, some of the registry and environment settings required by DTS are removed if you upgrade the last instance of SQL Server 2000 on the computer to SQL Server 2005 during or after setup. To restore these settings, locate "Microsoft SQL Server 2005 Backward compatibility" in the Add or Remove Programs list in the Control Panel, click the Change button, and select the Repair option on the Program Maintenance page of the wizard.

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

The backward compatibility files do not include the SQL Server 2000 Data Mining Prediction Query task.

Running DTS Packages from the Command Prompt

You can run SQL Server 2000 DTS packages from the command prompt. SQL Server 2005 Integration Services (SSIS) installs the SQL Server 2000 dtsrun.exe utility when it installs DTS run-time support.

Running DTS Packages from Integration Services Packages

You can also run packages that were created by using SQL Server 2000 tools as part of a SQL Server 2005 data transformation solution. Integration Services provides the Execute DTS 2000 Package task for running DTS packages from within SQL Server 2005 Integration Services packages.

For more information, see Execute DTS 2000 Package Task.

Installing Design-time Support for SQL Server 2000 DTS Packages

SQL Server 2005 Integration Services (SSIS) does not install the DTS designer. An updated version of the DTS package designer is available as a separate Web download. Download the Microsoft SQL Server 2000 DTS Designer Components.

For information on managing certain DTS application properties that affect the DTS design environment, see Setting DTS Application Properties.

Migrating Command Lines for Executing Packages

To understand how the command prompt options used with the SQL Server 2000 dtsrun utility map to the command prompt options used with the SQL Server 2005 dtexec utility, see dtsrun to dtexec Command Option Mapping.

Migrating DTS Packages

SQL Server 2005 provides the Package Migration Wizard for migrating SQL Server 2000 DTS packages to the Integration Services package format. Many packages can be fully migrated to SQL Server 2005, especially DTS packages that use tasks, connections, and other objects that have an equivalent in SQL Server 2005. Other packages can be migrated successfully by encapsulating certain tasks or features that cannot be migrated within an Execute DTS 2000 Package task. Before running the Package Migration Wizard, you may want to install and run Upgrade Advisor to analyze your existing packages for anticipated migration issues.

For more information about Upgrade Advisor, see Using Upgrade Advisor to Prepare for Upgrades.

Note:
The Package Migration Wizard is available in the Standard, Enterprise, and Developer Editions of SQL Server 2005.

Note:
Upgrade Advisor cannot analyze, and the 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 Package Migration Wizard can migrate, SQL Server 7.0 DTS packages that are saved as structured storage files, or that are saved in the msdb database of an instance of SQL Server 7.0 that has been upgraded to SQL Server 2005.

It is not possible to save or export Integration Services packages to the SQL Server 2000 DTS package format.

For more information, see Migrating Data Transformation Services Packages.

Support for SQL Server 2000 DTS Packages in SQL Server 2005 Express Edition

SQL Server 2005 Express Edition does not include Integration Services or support for SQL Server 2000 DTS packages.

  • To run existing DTS packages on a SQL Server 2005 Express Edition server, you must leave the SQL Server 2000 client tools or the DTS redistributable files on the server, or reinstall them. SQL Server 2005 Express Edition does not include the DTS runtime.
  • To edit existing DTS packages on a SQL Server 2005 Express Edition server, you must use SQL Server 2000, or edit the packages remotely from a server running SQL Server 2005 Workgroup, Standard, Enterprise, or Developer Edition. SQL Server 2005 Express Edition does not include SQL Server Management Studio or BI Development Studio.
  • To migrate existing DTS packages to SQL Server 2005 Integration Services, you must use SQL Server 2005 Standard, Enterprise, or Developer Edition. Other SQL Server 2005 editions do not include the Integration Services Package Migration Wizard.

The Import and Export Utility that is included with SQL Server 2005 Express Edition is not the SQL Server Import and Export Wizard and does not use Integration Services.

Support for Meta Data Services (Repository) Packages

SQL Server 2000 Meta Data Services, commonly known as the Repository, is a deprecated component. SQL Server 2005 does not install or use the Repository.

The SQL Server 2000 Data Transformation Services (DTS) designer and the dtsrun.exe utility continue to support DTS packages that were saved to Meta Data Services.

SQL Server 2005 Integration Services supports the Repository only in the Upgrade Advisor and the Package Migration Wizard, and only if SQL Server 2000, the SQL Server 2000 tools, or the Repository redistributable files are installed on the local computer. When the Repository files are present, the Upgrade Advisor can scan, and the Package Migration Wizard can migrate, DTS packages that were saved to Meta Data Services. When the Repository files are not present, the Upgrade Advisor can only scan, and the Package Migration Wizard can only migrate, DTS packages that were saved to SQL Server or to structured storage files.

The Integration Services Execute DTS 2000 Package task cannot execute a DTS package that was saved to the Repository. However the SQL Server 2000 DTS runtime can execute Repository packages. As a workaround for this limitation in Integration Services, when the Repository files are present, you can create a new SQL Server 2000 DTS package as a wrapper and save this new package to SQL Server or as a structured storage file. Use an Execute Package task in the new DTS package to execute the Repository package, and use the Execute DTS 2000 Package task in the Integration Services package to execute the new wrapper package.

Support for ODBC Destinations

SQL Server 2005 Integration Services has an ODBC source component, for loading data from ODBC data sources, but does not have a corresponding ODBC destination component, for saving data to ODBC destinations. It is possible to create an ad hoc ODBC destination for use within a single package by using the Script component. For more information, see Creating an ODBC Destination with the Script Component.

Change History

Release History

17 July 2006

Changed content:
  • Added section about ODBC destinations.
  • Added section about compatibility of DTS and SSIS packages.
  • Noted restriction on exporting packages to DTS 2000 format, and on design-time support for 2000 packages.

14 April 2006

Changed content:
  • Added list of all the operations that can be performed on DTS packages with SQL Server 2005 tools.
  • Added section on Managing DTS Packages.
  • Added section on Running DTS Packages from the Command Prompt.
  • Added note about Upgrade Advisor to section on migration.
  • Provided link for download of DTS designer components.

5 December 2005

Changed content:
  • Added information about downloading the DTS Designer and configuring its options.
  • Added note and link about migrating dtsrun.exe command lines.
  • Added information about support for migration from SQL Server 7.0.

See Also

Concepts

Known Package Migration Issues

Other Resources

dtsrun to dtexec Command Option Mapping
Integration Services Considerations on 64-bit Computers