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

ActiveX Script Task

Bulk Insert task

Bulk Insert Task

Copy SQL Server Objects task

Transfer SQL Server Objects Task

Data Mining Prediction task

Data Mining Query Task

Execute Package task

Execute DTS 2000 Package Task

Execute Process task

Execute Process Task

Execute SQL task

SSIS Execute SQL Task

File Transfer Protocol task

FTP Task

Message Queue task

Message Queue Task

Send Mail 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 Flow Task and Data Flow Elements.

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.