Migrating Data Transformation Services Packages
SQL Server 2005 Integration Services (SSIS) provides several options for preserving solutions created by using the SQL Server 2000 Data Transformation Services (DTS) tools and object model. You can migrate DTS packages to the SQL Server 2005 format, continue to run DTS packages by using the SQL Server 2000 DTS runtime, or incorporate DTS packages into SQL Server 2005 Integration Services solutions by using the Execute DTS 2000 Package task.
When you migrate SQL Server 2000 DTS packages to the SQL Server 2005 Integration Services format, the structure of the migrated package and the completeness of the migration depend on the contents of the original package.
Note
The Package Migration Wizard leaves the original DTS packages intact and unchanged.
The possible outcomes of package migration include the following:
- Packages can be migrated successfully and completely because they contain only tasks and use only features that map to SQL Server 2005 Integration Services (SSIS) tasks and features.
- Packages contain SQL Server 2000 DTS tasks and features that cannot be migrated directly to SQL Server 2005 Integration Services (SSIS) tasks and features, but package functionality is preserved through the use of encapsulated SQL Server 2000 packages.
Note
In some cases, such as when the package name contains invalid characters (/ \ : [ ] . =), or when the package contains unregistered objects, the Package Migration Wizard cannot perform the migration until these issues are resolved.
If all the tasks in the package can be mapped to new tasks in SQL Server 2005, the structure of the migrated package is very similar to the structure of the SQL Server 2000 package. Conversely, if tasks do not map directly to SQL Server 2005 tasks, the migration changes the package structure. Each non-migrated task retains its SQL Server 2000 format and is encapsulated within an Execute DTS 2000 task. Custom tasks in packages are also migrated this way. For more information, see Migrating Tasks.
Other package elements such as precedence constraints, connections, and variables are migrated to the equivalent element in SQL Server 2005. For more information, see Migrating Precedence Constraints, Migrating Connections, and Migrating Variables.
Package passwords are not migrated with packages that are migrated as SQL Server 2005 Integration Services (SSIS) packages. However, SQL Server 2005 supports package passwords in SQL Server 2000 packages and within the Execute DTS 2000 Package task. An Execute DTS 2000 Package task within a SQL Server 2005 package can pass the package password to a SQL Server 2000 package. For more information, see Migrating Passwords.
The ActiveX Script task in SQL Server 2005 is frequently able to run the script code that is migrated from the ActiveX scripts in SQL Server 2000 DTS packages without additional revision. However, references to SQL Server 2000 DTS package objects are not supported. For more information, see Migrating Scripts.
Text annotations entered in the DTS Designer, and package logging and error handling settings, are not migrated.
After migrating packages, the migrated packages should be opened to check for issues, including validation failures. For example, some packages rely on preceding tasks within the same package to create or prepare data sources or data destinations, and may fail validation when these objects cannot be located. A validation failure of this sort can be resolved by setting the DelayValidation property to true on the task or other container object, or by setting the ValidateExternalMetadata property to false on the affected data flow component.
Note
After migrating packages from Management Studio, connect to the local instance of Integration Services in Object Explorer to see the migrated packages. If you selected SQL Server as the destination, the migrated packages will be visible under the MSDB node. If you selected a file system folder as the destination, right-click the File System node and then select Import Package to display the migrated packages. After you migrate packages from BI Development Studio, the migrated packages are saved to the file system folder that you specified and added to the open project under the SSIS Packages node. DTS packages cannot be opened or edited in BI Development Studio.
In This Section
| Topic | Description |
|---|---|
Describes the migration process that the Package Migration Wizard provides. |
|
Lists known package migration issues that the Package Migration Wizard or the Upgrade Advisor may identify. |
|
Describes how the Package Migration Wizard migrates tasks, and explains the steps that you can take after migration to repair tasks or enhance task behavior. |
|
Describes how the Package Migration Wizard migrates precedence constraints, and explains the steps that you can take after migration to enhance precedence constraint behavior. |
|
Describes how the Package Migration Wizard migrates variables, and explains the steps that you can take after migration to repair or enhance variable behavior. |
|
Describes how the Package Migration Wizard migrates connections, and explains the steps that you can take after migration to edit connection properties. |
|
Describes how the Package Migration Wizard migrates passwords, and explains other steps that you can take after migration to improve security. |
|
Describes how the Package Migration Wizard migrates ActiveX scripts. |
Change History
| Release | History |
|---|---|
17 July 2006 |
|
See Also
Concepts
SQL Server 2005 Integration Services Backward Compatibility
Other Resources
SQL Server Integration Services