SQL Server 2005 Integration Services Backward Compatibility

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

• Managing DTS packages
• Run-time support for DTS packages
  • Installing run-time support for DTS packages
  • Running DTS packages from the command prompt
  • Running DTS packages from SQL Server Agent jobs
  • Running DTS packages from Integration Services packages
• Design-time support for DTS packages
• Migrating
  • Migrating dtsrun commands to dtexec commands
  • Migrating DTS packages to Integration Services packages
• Limited support
  • Support for Integration Services in SQL Server 2005 Express Edition
  • Support for Repository packages
  • Support for ODBC destinations

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.

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.

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 SQL Server Agent Jobs

To run a SQL Server 2000 DTS package from a SQL Server Agent job, you cannot use a job step of type, SQL Server Integration Services Package. Instead, use a job step of type, Operating system (CmdExec), and run the dtsrun.exe utility as the command.

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

DTS packages cannot be opened or edited directly in Business Intelligence Development Studio. In addition, SQL Server 2005 does not install the DTS package designer for editing DTS packages. However, you can download and install an updated version of the DTS package designer, and use this updated version to modify DTS packages. To download the updated DTS package designer, visit the Microsoft Web page, Feature Pack for Microsoft SQL Server 2005, and then download the Microsoft SQL Server 2000 DTS Designer Components. After installing this download, you can view and edit DTS packages from Management Studio, or from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio.

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.

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.

See Also

Concepts

Known Package Migration Issues

Other Resources

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

Help and Information

Getting SQL Server 2005 Assistance

Change History