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

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 SQL Server 2005 Integration Services Backward Compatibility.

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.

Note

The DTS 2000 run-time engine must be installed on the computer to run DTS 2000 packages. For more information, see How to: Ensure Support for Data Transformation Services 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, SQL Server 2005 does not install the DTS package designer that is required to modify DTS packages. However, you can download and install an updated version of the DTS package designer, and use this updated version to modify DTS packages. To download the updated DTS package designer, visit the Microsoft Web page, Feature Pack for Microsoft SQL Server 2005, and then download the Microsoft SQL Server 2000 DTS Designer Components. 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 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 2005. For more information, see Migrating Data Transformation Services Packages.

Other Related Tasks

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

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

• Execute Package Task
• Execute Process Task

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.

Note:

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:

• Execute DTS 2000 Package Task Editor (General Page)
• Execute DTS 2000 Package Task Editor (Inner Variables Page)
• Execute DTS 2000 Package Task Editor (Outer Variables Page)
• Expressions Page

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

• How to: Set Task Properties

Configuring the Execute DTS 2000 Package Task Programmatically

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

• Exec80PackageTask

See Also

Concepts

Integration Services Tasks
Creating Package Control Flow
Integration Services Considerations on 64-bit Computers

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
15 September 2007	Changed content: Added note about how DTS packages cannot be run in 64-bit mode on a 64-bit computer. Added note about how to download the DTS package designer that is required to edit DTS packages.
17 July 2006	Changed content: Added table of custom log entries.
5 December 2005	Changed content: Provided workaround for running SQL Server 2000 DTS packages saved to Meta Data Services.