DTS Package Workflow

Data Transformation Services (DTS) steps and precedence constraints order work items in a DTS package. You can design DTS package workflow graphically, through DTS Designer, or programmatically. For more information, see Creating DTS Package Workflow and Tasks.

You also can use a Microsoft® ActiveX® script to customize step execution. For more information, see Using ActiveX Scripts in a DTS Workflow.

DTS Package Steps

Steps control the order in which tasks are executed in a DTS package. Steps represent the execution units in the DTS object model, and they define which tasks execute in what sequence when the package is run.

In DTS Designer, you do not manipulate steps directly. Instead, you manipulate tasks on the DTS Designer design sheet and use precedence constraints to control the sequence in which the tasks execute. When you place a task on the design sheet, a step is automatically added to the package, for a step references a task.

When creating a package programmatically, you can control the relationship between a step and a task more precisely. You can create multiple steps for different package operations and associate the execution of those steps with a single task. For example, suppose you write a package in Microsoft Visual Basic® and specify in several parts of the package that errors can be generated. By linking the steps associated with those errors, you can make the different types of errors execute the same Send Mail task. That Send Mail task can send an e-mail notifying the database administrator (DBA) that the package failed.

DTS Designer allows you to execute an individual package step. This action is useful for testing and troubleshooting individual steps without having to run the entire package.

To execute a single package step in DTS Designer

Enterprise Manager

Precedence Constraints

Precedence constraints sequentially link tasks in a package. In DTS, you can use three types of precedence constraints, which can be accessed either through DTS Designer or programmatically:

• Unconditional.

  If you want Task 2 to wait until Task 1 completes, regardless of the outcome, link Task 1 to Task 2 with an unconditional precedence constraint.

• On Success.

  If you want Task 2 to wait until Task 1 has successfully completed, link Task 1 to Task 2 with an On Success precedence constraint.

• On Failure.

  If you want Task 2 to begin execution only if Task 1 fails to execute successfully, link Task 1 to Task 2 with an On Failure precedence constraint. If you want to run an alternative branch of the workflow when an error is encountered, use this constraint.

The following diagram illustrates the relationship between steps and tasks in DTS Designer and shows how precedence constraints determine step execution.

Step 1, Step 2, and Step 3 indicate where the package steps occur. After the package is opened and all connections are made, the flow of execution is as follows:

• Step 1: An Execute SQL task drops and re-creates the destination table.
  
  
• Step 2: If the destination table is successfully dropped and re-created, data is copied from a table in a Microsoft Access database to a table in a Microsoft SQL Server™ database. The On Success precedence constraint, shown by a green striped arrow pointing from the Execute SQL task toward the transformation, establishes the relationship between those two steps. The Transform Data task is shown by the dark gray arrow pointing from the Microsoft Access database to the SQL Server database.

  Because connections are placed at both ends of the Transform Data task arrow on the design sheet, the two precedence constraints actually touch the connection icons rather than the Transform Data task itself. In Step 2, the icons represent connections, not tasks. The Transform Data task, represented by the dark gray line, is the only task that DTS Designer represents by a line rather than an icon. For all other tasks in DTS Designer, you connect precedence constraints to the task icon itself, rather than to a connection icon.

• Step 3: If the data copying task fails, a notification e-mail is sent to a DBA. The On Failure precedence constraint pointing from the transformation to the Send Mail task establishes the relationship between those two steps.

Using Multiple Precedence Constraints

You can issue multiple precedence constraints on a task. For example, in the following illustration, Task C could have both an On Success constraint from Task A and an On Failure constraint from Task B. In these situations, DTS assumes a logical "And" relationship. Therefore, Task A must successfully execute and Task B must fail for Task C to begin execution.