Parallel Execution

Time or resource constraints may require that Data Transformation Services (DTS) tasks execute in parallel. As a result, transaction configuration becomes more complex. To avoid anomalous results, use DTS package failure to roll back the transaction when steps execute in parallel.

The following diagram shows a parallel ABC package.

All three tasks still join the same transaction, but now, tasks A and C are supposed to start simultaneously. In this situation, DTS behavior differs, depending on the connections used by these tasks:

• If tasks A, B, and C all use the same connection, DTS will serialize their execution in spite of the parallel construction. Precedence relationships are enforced, but otherwise, order of execution is undefined.
  
  
• If tasks A and B use Connection 1 and task C uses Connection 2, then:
  • If Connection 1 and Connection 2 are on the same instance of Microsoft® SQL Server™ 2000, the package fails when the second task attempts to join the transaction.

    Therefore, you must use precedence relationships or the Execute on main package thread workflow property to ensure that no two tasks execute simultaneously as part of the package transaction.

  • If Connection 1 and Connection 2 are not on the same instance of SQL Server 2000, tasks A and C execute in parallel, as expected.

Rollback and the Package Transaction

If you do not attend carefully to package configuration, your package may produce unanticipated results. For example, in the preceding diagram, you want all three tasks to join the same transaction. However, incorrect settings for transaction properties could result in tasks A and C rolling back, while changes made by task B are committed. For example, consider what happens if tasks A and C start simultaneously and then task C fails and rolls back before A completes:

• Task A is not canceled in mid-execution but continues to its normal conclusion.
  
  
• Any changes made by task A or C are rolled back after task A completes.
  
  
• The rollback has no effect on the success or failure status of task A. If task A encounters no problems, it completes successfully as usual.
  
  
• If the package is not configured to fail on the first error, task B will commence as usual on the successful completion of task A. Because there is no active transaction when task B starts, a new package transaction is created. If the Commit on successful package completion check box is selected, changes made by task B will be committed in spite of the earlier failed transaction.

Enforcing a Single Package Transaction

If a transaction includes several tasks executing in parallel, when one task fails, any changes must roll back and execution must stop. New tasks must not commence. To enforce a single package transaction when multiple tasks may be active, fail the package and then roll back the transaction on package failure.

Configure the Parallel ABC package as follows:

• Select the Fail package on first error check box for the package.
  
  
• Select the Use transactions check box for the package.
  
  
• Select the Commit on successful package completion check box for the package.
  
  
• Select the Join transaction if present check box for each step.
  
  
• Clear the Commit transaction on successful completion of this step check box for each step.
  
  
• Clear the Rollback transaction on failure check box for each step.

Selecting the Fail package on first error check box triggers an unsuccessful package completion as of the first step failure. As a consequence, no more tasks are started, and any updates in the existing package transaction are rolled back.