DTS Transaction Fundamentals

Over the course of the execution of a single package, transactions are initiated, joined, and then committed or rolled back. After a commit or rollback operation, the cycle may repeat.

If the Data Transformation Services (DTS) package includes Execute Package tasks, transaction behavior can differ from that described in this topic. For more information about transactions and the Execute Package task, see Inherited Transactions.

Initiating a New Package Transaction

No package transaction exists until a step attempts to join it. At this point, a new transaction is created for the package, and the step proceeds with its attempt. If other steps attempt to join the package transaction before the first transaction has committed or rolled back, they are enlisted in the first transaction. Although a package may initiate several transactions, only one package transaction can be active at a time.

Joining the Package Transaction

The attempt to join the package transaction takes place only after any workflow script has been processed. If a step joins the package transaction, any updates made by the step accumulate in the package transaction. If a step does not join the package transaction, database changes are committed in autocommit mode: one at a time, as they are requested.

In order to join a transaction successfully, the package step must:

• Be one of several supported task types. For example, the Execute SQL task is supported, but the Send Mail task is not.
  
  
• Use supported connection types on outputs. For example, an instance of Microsoft® SQL Server™ 2000 is supported, but a connection to a Microsoft Excel 2000 worksheet is not.

If the preceding conditions are not met, the attempt to join the package transaction fails, and the package halts at runtime. For more information about supported task and connection types, see Supported Task Types and Supported Connection Types.

In DTS Designer, a step attempts to join the package transaction if you:

• Select the Use transactions check box in the DTS Package Properties dialog box.
  
  
• Select the Join transaction if present check box in the Workflow Properties dialog box of a step.

  Note  When a step joins the package transaction, each connection used by the step is enlisted in the distributed transaction. All updates for such a connection accumulate in the package transaction, even if they originate in a step that did not explicitly join the package transaction. Therefore, to make transactional and non-transactional updates to the same database from one package, you must use two connections.

Committing and Rolling Back Package Transactions

When a package transaction is committed, any accumulated updates are made permanent. When a package transaction is rolled back, any accumulated updates are reversed.

A package transaction is committed when either of the following events occurs:

• A step completes successfully and the Commit transaction on successful completion of this step check box is selected.
  
  
• The package completes successfully and the Commit on successful package completion check box is selected.

The current package transaction is rolled back when any of the following events occur:

• The package fails.
  
  
• The package finishes and the Commit on successful package completion check box is cleared for the package.
  
  
• A step fails and the Rollback transaction on failure check box is selected for the step.

  Note  Some operations can leave the current transaction in an invalid state (for example, failure during a commit or rollback or a rollback in a subpackage). Attempts to join or commit an invalid transaction fail the package. To terminate the invalid transaction and so allow a new package transaction to start, trigger a rollback in the controlling package.