Upgrading or Migrating Data Transformation Services
Note
We recommend that you review Version and Edition Upgrades for general information about upgrade scenarios for the Microsoft SQL Server 2005 products and technologies.
If you are a previous or current user of Microsoft SQL Server 2000 Data Transformation Services (DTS), you have the following options when you install Microsoft SQL Server 2005 Integration Services (SSIS):
- Installing Support for SQL Server 2000 Data Transformation Services Packages
- Upgrading an earlier version of Data Transformation Services to SSIS
- Migrating existing Data Transformation Services packages to SSIS
Important
Regardless of which installation option you choose, you should be aware that some SQL Server 2000 Data Transformation Services features and functionality have been discontinued, deprecated, or changed. For more information, see SQL Server 2005 Integration Services Backward Compatibility.
Known Upgrade Issues
Since Integration Services is an entirely new product, and not a version upgrade, you will not encounter software conflicts when upgrading to Integration Services. However, before upgrading Data Transformation Services to SQL Server 2005 Integration Services, we recommend that you review SQL Server 2005 Integration Services Backward Compatibility.
Pre-Upgrade Checklist
Before upgrading Data Transformation Services to SQL Server 2005 Integration Services, review the following requirements:
- Hardware and Software Requirements for Installing SQL Server 2005
- Check Parameters for the System Configuration Checker
- Security Considerations for a SQL Server Installation
- Using Upgrade Advisor to Prepare for Upgrades
Installing Support for SQL Server 2000 Data Transformation Services Packages
You can run SQL Server 2000 Data Transformation Services (DTS) packages and Integration Services packages on the same computer.
When you select Integration Services for installation, Setup also installs support for SQL Server 2000 Data Transformation Services (DTS) packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Run-time support has been updated to allow DTS packages to access SQL Server 2005 data sources. If you are not installing Integration Services, but you need support for DTS packages, then you need to make sure that Legacy Components is selected on the Feature Selection page.
Note
Although support for SQL Server 2000 Data Transformation Services (DTS) 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 the SQL Server 2000 Analysis Services Processing task and its dependency, Decision Support Objects (DSO). However they do not include the SQL Server 2000 Data Mining Prediction Query task.
Note
SQL Server 2005 does not install the DTS package designer for editing DTS packages. For more information, see SQL Server 2005 Integration Services Backward Compatibility. DTS packages cannot be opened or edited in BI Development Studio.
When you select Integration Services for installation, the Package Migration Wizard is also installed. You can optionally use the Package Migration Wizard to upgrade SQL Server 2000 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. For more information, see Migrating Data Transformation Services Packages.
Note
The Package Migration Wizard is available in the Standard, Enterprise, and Developer Editions of SQL Server 2005.
For more information, see How to: Ensure Support for Data Transformation Services Packages.
Upgrading an Earlier Version of Data Transformation Services to Integration Services
The process of upgrading SQL Server 2000 Data Transformation Services to SQL Server 2005 Integration Services consists of the installation of SQL Server 2005 Integration Services and, optionally, the migration of existing DTS packages to the SQL Server 2005 Integration Services package format. This process uses software tools in addition to Setup itself.
To upgrade an instance of SQL Server 2000 Data Transformation Services to SQL Server 2005 Integration Services, follow these steps:
- Install SQL Server 2005 Integration Services by using the Installation Wizard or an alternate installation method. For more information, see Installing SQL Server Integration Services by Using Setup and Installing SQL Server Integration Services from the Command Prompt.
- Optionally, use the Package Migration Wizard to migrate SQL Server 2000 DTS packages to SQL Server 2005 Integration Services. This step recreates the packages in the new package format while leaving the original packages intact. When you select Integration Services for installation, the Package Migration Wizard is also installed. For more information, see Migrating Data Transformation Services Packages.
For more information, see How to: Upgrade from Data Transformation Services to Integration Services and SQL Server 2005 Integration Services Backward Compatibility.
Migrating Existing Data Transformation Services Packages to Integration Services
You can optionally use the Package Migration Wizard to upgrade SQL Server 2000 DTS packages to the SQL Server 2005 Integration Services format. During migration, the wizard copies SQL Server 2000 DTS packages and then re-creates them in the SQL Server 2005 Integration Services format. The source packages are left intact and are not modified.
When you select Integration Services for installation, the Package Migration Wizard is also installed.
Note
The Package Migration Wizard is available in the Standard, Enterprise, and Developer Editions of SQL Server 2005.
For more information, see Migrating Data Transformation Services Packages, How to: Migrate Data Transformation Services Packages to Integration Services, and Known Package Migration Issues.
See Also
Concepts
Upgrading dtsrun Command Lines