Known DTS Package Migration Issues
This is a summary of the issues that you may experience when migrating Microsoft SQL Server 2000 Data Transformation Services (DTS) packages to the Integration Services format by using the Package Migration Wizard.
The Package Migration Wizard migrates certain SQL Server 2000 DTS features that have no direct equivalent in Integration Services by encapsulating them in an Execute DTS 2000 Package task. Although in many cases you can run these migrated packages without error, you must eventually replace the functionality encapsulated within the Execute DTS 2000 Package task.
After migrating packages, 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.
For general information about support for DTS in Integration Services, see Support for SQL Server 2000 DTS in SQL Server 2008 R2.
Known Issues Detected by Upgrade Advisor
The following list presents the migration issues and a brief description of each as they appear in the report generated by the SQL Server Upgrade Advisor. The Package Migration Wizard and Upgrade Advisor identify and report the same potential package migration issues.
Issue Title |
Issue Description |
|---|---|
Reconfigure transaction settings after package migration |
The Integration Services Package Migration Wizard does not migrate transaction settings in SQL Server 2000 Data Transformation Services (DTS) packages. After migration, you must manually edit the resulting SSIS package to restore former package behavior. Corrective action: After migration, re-create the desired transaction settings on the package by setting the appropriate transaction-related properties on the package and its components in Business Intelligence Development Studio. For more information, see Ensuring Data Integrity by Using Transactions. |
Replace functionality of ActiveX script attached to package steps after package migration |
The Integration Services Package Migration Wizard does not migrate ActiveX scripts associated with steps in SQL Server 2000 Data Transformation Services (DTS) packages. After migration, you must manually edit the resulting SSIS package to restore former package behavior. Corrective action: After migration, replace the missing script with a new Script task or other Integration Services objects as appropriate. For more information, see Script Task. |
Replace functionality of Analysis Services task encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate the Analysis Services task in SQL Server 2000 Data Transformation Services (DTS) packages. The migrated package will contain encapsulated DTS functionality which must eventually be replaced. Corrective action: After migration, convert the Execute DTS 2000 Task that encapsulates the Analysis Services task to an Integration Services Analysis Services Processing task. For more information, see Analysis Services Processing Task. |
Replace functionality of complex Data Transformation task encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate complex Data Transformation tasks in SQL Server 2000 Data Transformation Services (DTS) packages. The migrated package will contain encapsulated DTS functionality which must eventually be replaced. Corrective action: After migration, rewrite the complex Data Transformation task by using Integration Services data flow sources, transformations, and destinations. For more information, see Designing Package Data Flow and Data Flow Elements. |
Replace functionality of custom task encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate custom tasks in SQL Server 2000 Data Transformation Services (DTS) packages. The migrated package will contain encapsulated DTS functionality which must eventually be replaced. Corrective action: After migration, re-create the logic of the custom task by using a Script task or Script component, or a custom component, if the desired functionality is not available in a built-in Integration Services component or combination of components. For information about programming solutions, see Developer's Guide (Integration Services). |
Replace functionality of Data Driven Query task encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate Data Driven Query tasks in SQL Server 2000 Data Transformation Services (DTS) packages. The migrated package will contain encapsulated DTS functionality that must eventually be replaced. Corrective action: After migration, re-create the functionality of the Data Driven Query task by using Integration Services data flow components. Consider the Slowly Changing Dimension Transformation, the Conditional Split Transformation, or the OLE DB Command Transformation when replacing the functionality of Data Driven Query tasks. |
Replace functionality of Data Mining Prediction Query task encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate the Data Mining Prediction Query task in SQL Server 2000 Data Transformation Services (DTS) packages. The migrated package will contain encapsulated DTS functionality which must eventually be replaced. SQL Server does not install support for the SQL Server 2000 Data Mining Prediction Query task; therefore the migrated package will not run after upgrade in the absence of SQL Server 2000 Analysis Services. Corrective action: After migration, convert the Execute DTS 2000 Task that encapsulates the Data Mining Prediction Query task to an Integration Services Data Mining Query task or Data Mining Query transformation. For more information, see Data Mining Query Task and Data Mining Query Transformation. |
Replace functionality of Dynamic Properties task after package migration |
The Integration Services Package Migration Wizard does not migrate Dynamic Properties tasks in SQL Server 2000 Data Transformation Services (DTS) packages. After migration, you must manually edit the resulting SSIS package to restore former package behavior. Corrective action: After migration, re-create the functionality of the Dynamic Properties task by using Integration Services features such as variables, property expressions, and package configurations. For more information, see Integration Services Variables and Using Variables in Packages; Using Property Expressions in Packages; and SSIS Package Configurations. |
Replace functionality of Execute Package task (with Meta Data Services package) encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate Execute Packages tasks that load Meta Data Services packages. The migrated package will contain encapsulated DTS functionality which must eventually be replaced. SQL Server does not install support for SQL Server 2000 Meta Data Services; therefore the migrated package will not run after upgrade in the absence of the SQL Server 2000 Client Tools. Corrective action: After migration, move the Meta Data Services package to SQL Server storage (in the msdb database) or to a structured storage file, and convert the Execute DTS 2000 Task that encapsulates the Execute Package task that loads a Meta Data Services package to an Execute DTS 2000 Task that loads the package from SQL Server or from a structured storage file. For more information, see Saving Packages. |
Replace functionality of Parallel Data Pump task encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate Parallel Data Pump tasks in SQL Server 2000 Data Transformation Services (DTS) packages. The migrated package will contain encapsulated DTS functionality which must eventually be replaced. Corrective action: After migration, re-create the functionality of the Parallel Data Pump task by using Integration Services data flow components. For more information, see Designing Package Data Flow and Data Flow Elements. |
Replace functionality of UDL connection encapsulated in Execute DTS 2000 Package task after package migration |
The Integration Services Package Migration Wizard does not migrate tasks that obtain their connection information from Microsoft Data Link (.udl) files. The migrated package will contain encapsulated DTS functionality which must eventually be replaced. Corrective action: After migration, convert the Execute DTS 2000 Task that encapsulates the UDL file connection to an equivalent Integration Services task that uses an Integration Services connection manager. For more information, see Integration Services Connections and Adding Connection Managers. |
Rewrite ActiveX script code that access the DTS object model after package migration |
The Integration Services Package Migration Wizard does not migrate ActiveX Script code that accesses the DTS object model through the Parent property of the GlobalVariables collection in SQL Server 2000 Data Transformation Services (DTS) packages. After migration, you must manually edit the resulting SSIS package to restore former package behavior. Corrective action: After migration, modify the script code, or rewrite the ActiveX Script task to obtain the required information from the package in a different way: for example, by using variables. For more information, see Integration Services Variables and Using Variables in Packages. |
Known Issues Not Detected by Upgrade Advisor
The following issues are not detected or reported by the Package Migration Wizard or Upgrade Advisor.
Issue Title |
Issue Description |
|---|---|
Delete and recreate ODBC connections after package migration |
ODBC connections raise an error if you try to edit the connection after migration. Corrective action: If you have already migrated the package, you can delete and recreate the ODBC connection. This issue has been fixed in SQL Server Service Pack 1. Install Service Pack 1 or later to prevent this issue from recurring. |