Sequential Execution
For the simplest Data Transformation Services (DTS) package transaction configuration, organize steps sequentially. If you do this, you can roll back transactions at the step level.
The following diagram shows a sequential ABC package.
Three Execute SQL tasks are arranged in order with precedence relationships. Only on the success of one step is the following task started. If any one of the tasks fails, no more steps execute. The sequential organization of the package allows you to roll back the transaction immediately on failure.
All three tasks join the package transaction. If any task fails, the transaction rolls back, and the package halts. If task C completes successfully, the entire transaction is committed.
To run all three tasks as part of a single package transaction, do the following:
- Select the Use transactions check box for the package.
- Select the Join transaction if present check box for each step.
- Select the Rollback transaction on failure check box for each step.
- Select the Commit transaction on successful completion of this step check box for task C.