Share via

  • Article

Execute DTS 2000 Package Task

The Execute DTS 2000 Package task runs packages that were developed by using the SQL Server 2000 tools. By using this task, you can include SQL Server 2000 DTS packages in SQL Server data transformation solutions. A package can include both Execute Package tasks and Execute DTS 2000 Package tasks, because each type of task uses a different version of the run-time engine.

Important

The Execute DTS 2000 Package task is provided only for purposes of backward compatibility with the deprecated component, Data Transformation Services (DTS). For more information, see Data Transformation Services (DTS).

Important

SQL Server 2008 does not install run-time support for DTS packages. You have to install this run-time support before you can run DTS packages. For information about how to install support for DTS, see How to: Install Support for Data Transformation Services Packages.

Note

You cannot use the Execute DTS 2000 Package task in a package that is running in 64-bit mode on a 64-bit computer. There is no 64-bit design-time or run-time support for Data Transformation Services (DTS) packages that were created in earlier versions of SQL Server. For more information, see Support for SQL Server 2000 DTS in SQL Server 2008 R2.

The Execute DTS 2000 Package task runs SQL Server 2000 packages, but otherwise its features are similar to those of the Execute Package task. You can use the Execute DTS 2000 Package task for the following purposes:

  • Breaking down complex package workflow. This task lets you break down workflow into multiple packages, which are easier to read and maintain. For example, if you are loading data into a star schema, you can build a separate package to populate each dimension and the fact table.

  • Reusing parts of packages. Other packages can reuse parts of a package workflow. For example, you can build a data extraction module that can be called from different packages. Each package that calls the extraction module can perform different data scrubbing, filtering, or aggregation operations.

  • Grouping work units. Units of work can be encapsulated into separate packages and joined as transactional components to the workflow of a parent package. For example, the parent package runs the accessory packages, and based on the success or failure of the accessory packages, the parent package either commits or rolls back the transaction.

  • Controlling package security. Package authors require access to only a part of a multipackage solution. By separating a package into multiple packages, you can provide a higher level of security, because you can grant an author access only to relevant packages.

A package that executes other packages, such as the Execute DTS 2000 Package task, is typically referred to as the parent package, and the packages that a parent workflow runs are called child packages.

The Execute DTS 2000 Package task can run packages stored in the SQL Server msdb database, in structured storage files, or in Meta Data Services. If you choose a package stored in msdb or Meta Data Services, you must provide the name of the server and select an authentication method. The task supports Windows Authentication and SQL Server Authentication. If you can, use Windows Authentication to provide better security. If you choose a package stored in the file system, you must provide the name and location of the package. The package can reside anywhere in the file system; the package does not have to be in the same folder as the parent package.

Packages can be loaded into the Execute DTS 2000 Package task and stored internally, regardless of the original storage location or format. To load the SQL Server 2000 DTS package, or to open the DTS Designer to view or modify the child package, use the Execute DTS 2000 Package Task Editor dialog box provided in the SSIS Designer.

Note

DTS packages cannot be opened or modified directly in Business Intelligence Development Studio. In addition, neither SQL Server 2008, nor SQL Server 2005, installs the DTS package designer that is required to modify DTS packages. However, you can download and install the DTS package designer, and use it to modify DTS packages. For information about how to install design-time support for DTS packages, see How to: Install Support for Data Transformation Services Packages. After installing this download, you can view and modify DTS packages from Management Studio, or from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio.

The DTS package designer works with the SQL Server 2008 tools, but the designer's features have not been updated for SQL Server 2008.

The Execute DTS 2000 Package task can use inner and outer variables. Inner variables are variables in the child package; outer variables are variables in the parent package. Using inner variables, the Execute DTS 2000 Packages task can pass updated values to the global variables in the SQL Server 2000 DTS package. Using outer variables, the calling package can pass the values of variables to the child package. For example, you can use the values of outer variables in child package scripts. For more information, see Integration Services Variables and Using Variables in Packages.

Integration Services does not support Meta Data Services and an Integration Services package cannot run SQL Server 2000 DTS packages that are saved to Meta Data Services by using the Execute DTS 2000 Package task. The package must use the SQL Server 2000 DTS runtime to run packages in Meta Data Services. To run these packages, you create an SQL Server 2000 parent package that runs the package in Meta Data Services, and save the parent package to SQL Server or a structured storage file. The Execute DTS 2000 package can then run the parent package, which in turn can run the package in Meta Data Services.

Many DTS 2000 packages can be migrated to SQL Server. For more information, see Migrating Data Transformation Services Packages.

Integration Services includes tasks that perform workflow operations such as running SQL Server packages, executables, and batch files.

For more information about these tasks, click one of the following topics:

Custom Log Entries Available on the Execute DTS 2000 Task

The following table lists the custom log entries for the Execute DTS 2000 task. For more information, see Implementing Logging in Packages and Custom Messages for Logging.

Log entry

Description

ExecuteDTS80PackageTaskBegin

Indicates that the task began to run a DTS 2000 package.

ExecuteDTS80PackageTaskEnd

Indicates that the task finished.

NoteNote
The DTS 2000 package may continue to run after the task ends.

ExecuteDTS80PackageTaskTaskInfo

Provides descriptive information about the task.

ExecuteDTS80PackageTaskTaskResult

Reports the execution result of the DTS 2000 package that the task ran.

Configuring the Execute DTS 2000 Package Task

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in SSIS Designer, click one of the following topics:

For more information about how to set these properties in SSIS Designer, click the following topic:

Configuring the Execute DTS 2000 Package Task Programmatically

For more information about programmatically setting these properties, click the following topic:

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.