Branching on Transaction Failure
When tasks execute in parallel, you often have to fail the Data Transformation Services (DTS) package in order to prevent anomalous results. This poses a problem if you require an action on failure (for example, the dispatch of an e-mail message). Within a package, you cannot simultaneously fail the package and run extra steps.
Use the Execute Package task and package failure to conditionally run extra steps after transaction failure.
The following diagram shows a package executing the Run Parallel ABC package. Depending on the outcome, the package either continues processing or sends an error message. Although the Run Parallel ABC task appears in this example, the same procedure can be used to branch on the failure of any package, even those that do not use transactions.
The first step in the package executes the Parallel ABC package as a subpackage. The failure of step A, B, or C triggers subpackage failure, which in turn fails the Run Parallel ABC step. On failure of this step, Error Alert sends the e-mail message. If the subpackage completes successfully, however, Run Parallel ABC succeeds and More Work is commenced.
For the example, configure package properties as follows:
- Select the Use transactions check box.
- Clear the Fail package on first error check box.
Configure More Work workflow properties as follows:
- Select the Join transaction if present check box.
- Select the Commit transaction on successful completion of this step check box.
- Select the Rollback transaction on failure check box.
If Run Parallel ABC and More Work are to join the same transaction, configure Run Parallel ABC workflow properties as follows:
- Select the Join transaction if present check box.
- Clear the Commit transaction on successful completion of this step check box.
- Select the Rollback transaction on failure check box.
If Run Parallel ABC and More Work are to run in separate transactions, with changes committed after the first, you can delegate transaction processing to the subtask. To do this, configure Run Parallel ABC workflow properties as follows:
- Clear the Join transaction if present check box.
- Clear the Commit transaction on successful completion of this step check box.
- Clear the Rollback transaction on failure check box.