Migrating Tasks
Many SQL Server 2000 Data Transformation Services (DTS) tasks map directly to Integration Services tasks, and migrating a package to Integration Services replaces the SQL Server 2000 version of a task with its Integration Services equivalent.
The following table lists the direct mappings between task versions.
DTS task |
Integration Services task |
|---|---|
ActiveX Script task |
|
Bulk Insert task |
|
Copy SQL Server Objects task |
|
Data Mining Prediction task |
|
Execute Package task |
|
Execute Process task |
|
Execute SQL task |
|
File Transfer Protocol task |
|
Message Queue task |
|
Send Mail task |
|
Transfer Databases, Transfer Error Messages, Transfer Jobs, Transfer Logins, and Transfer Master Stored Procedures tasks |
Transfer SQL Server Objects Task, Transfer Database Task, Transfer Error Messages Task, Transfer Jobs Task, Transfer Logins Task, and Transfer Master Stored Procedures Task |
Note
ActiveX Script tasks that try to access the package object model by using the Parent property of the GlobalVariables collection will fail after package migration.
Some SQL Server 2000 tasks do not map directly to Integration Services tasks:
Analysis Services task (because the Integration Services Analysis Services task does not work with a SQL Server 2000 instance of Analysis Services)
Data Driven Query task
Dynamic Properties task
Transform Data task
In SQL Server, some of these tasks are replaced by non-task features. The following table lists the mapping between the SQL Server 2000 tasks and their equivalent SQL Server non-task feature.
DTS task |
Integration Services feature |
|---|---|
Dynamic Properties |
Configurations and property expressions. For more information, see Creating Package Configurations. |
Transform Data |
|
Data Driven Query |
Consider the Slowly Changing Dimension Transformation, the Conditional Split Transformation, and the OLE DB Command Transformation when replacing the functionality of Data Driven Query tasks. |
Custom Tasks
A package that contains custom tasks is migrated to SQL Server in a special manner. A SQL Server 2000 package is created for each custom task, and an Execute DTS 2000 Package task that calls the SQL Server 2000 package is added to the migrated package. The new SQL Server 2000 packages, called intermediate packages, are embedded within the migrated package and are not saved separately. For example, a SQL Server 2000 package with three custom tasks becomes a SQL Server package that contains three Execute DTS 2000 Package tasks, where each task points to an embedded SQL Server 2000 package that encapsulates a custom task.
Data Pump Tasks
Three SQL Server 2000 tasks — Data Driven Query, Transform Data, and ParallelDataPump — are based on the SQL Server 2000 DTS data pump. With the exception of simple data transformation tasks, these tasks are migrated in the same way as custom tasks. The Package Migration Wizard creates a new SQL Server 2000 package for each task, and adds an Execute DTS 2000 Package task for each data pump task to the new package.
Dynamic Properties Task
Dynamic Properties tasks cannot be migrated to SQL Server. The Package Migration Wizard warns you if you try to migrate a package with a Dynamic Properties task. If you choose to migrate the package, the Dynamic Properties task is replaced by a placeholder task in the migrated package. The placeholder task is a Script task that lists the property-value pairs from the Dynamic Properties task that you may want to convert to script in the Script task. This information is written to the Script task in the form of comments in the script.
In SQL Server, configurations replace and enhance the functionality available in the Dynamic Properties task. For more information, see Creating Package Configurations.