Migrating Data Transformation Services Packages

Integration Services 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 Integration Services format, continue to run DTS packages by using the DTS runtime, or incorporate DTS packages into Integration Services solutions by using the Execute DTS 2000 Package task.

Note

For information on upgrading packages that were created in an earlier version of Integration Services, see Upgrading Integration Services Packages.

When you migrate DTS packages to the Integration Services format, the structure of the migrated package and the completeness of the migration depend on the contents of the original package. 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 Integration Services tasks and features.

  • Packages contain DTS tasks and features that cannot be migrated directly to Integration Services tasks and features, but package functionality is preserved through the use of encapsulated DTS packages.

  • 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.

Note

The Package Migration Wizard leaves the original DTS packages intact and unchanged.

If all the tasks in the package can be mapped to new tasks in Integration Services, the structure of the migrated package is very similar to the structure of the DTS package. Conversely, if tasks do not map directly to Integration Services tasks, the migration changes the package structure. Each non-migrated task retains its DTS 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 Integration Services. For more information, see Migrating Precedence Constraints, Migrating Connections, and Migrating Variables.

Package passwords are not migrated. However, Integration Services supports DTS package passwords in DTS packages and within the Execute DTS 2000 Package task. An Execute DTS 2000 Package task within an Integration Services package can pass the package password to a DTS package. For more information, see Migrating Passwords.

The ActiveX Script task in Integration Services is frequently able to run the script code that is migrated from the ActiveX scripts in DTS packages without additional revision. However, references to 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 directly in BI Development Studio.

For general information about support for DTS in Integration Services, see Support for SQL Server 2000 DTS in SQL Server 2008 R2.

In This Section

  • Using the DTS Package Migration Wizard
    Describes the migration process that the Package Migration Wizard provides.

  • Known DTS Package Migration Issues
    Lists known package migration issues that the Package Migration Wizard or the Upgrade Advisor may identify.

  • Migrating Tasks
    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.

  • Migrating Precedence Constraints
    Describes how the Package Migration Wizard migrates precedence constraints, and explains the steps that you can take after migration to enhance precedence constraint behavior.

  • Migrating Variables
    Describes how the Package Migration Wizard migrates variables, and explains the steps that you can take after migration to repair or enhance variable behavior.

  • Migrating Connections
    Describes how the Package Migration Wizard migrates connections, and explains the steps that you can take after migration to edit connection properties.

  • Migrating Passwords
    Describes how the Package Migration Wizard migrates passwords, and explains other steps that you can take after migration to improve security.

  • Migrating Scripts
    Describes how the Package Migration Wizard migrates ActiveX scripts.

  • How to: Migrate Data Transformation Services Packages to Integration Services
    Describes the steps for migrating DTS packages to the Integration Services format.