Configuring Properties for DTS Transactions
Several Data Transformation Services (DTS) package properties and workflow step properties are used to control transaction initiation, step participation in the transaction, and the final commit or rollback operation. These properties can be set from within DTS Designer or programmatically, through the package and step objects of the DTS object model.
DTS transaction settings fall into two groups. Package properties are global settings that affect transaction behavior across the entire package. Step properties operate at the level of the individual task. In the following list of DTS transaction settings, package properties are listed before step properties. For each transaction option, the corresponding DTS object model property is listed.
In the DTS Package Properties dialog box, on the General tab, you can find:
- Fail package on first error.
If this check box is selected, the first step failure triggers package failure, terminating all tasks. If cleared, the package continues to run after the first and subsequent step failures, always completing successfully, no matter how many errors occur.
In the DTS object model, set the FailOnError property of the package object. For more information, see FailOnError Property.
In the DTS Package Properties dialog box, on the Advanced tab, you can find:
- Use transactions.
If this check box is selected, transactions are enabled. If cleared, no package transaction is created, and requests by steps to join the transaction are ignored.
In the DTS object model, set the UseTransaction property of the package object. For more information, see UseTransaction (DTSMQMessage) Property.
- Commit on successful package completion.
If this check box is selected, updates pending in an open package transaction are committed when a package finishes executing and one or both of the following is true:
- No steps failed.
- The Fail package on first error check box is cleared.
If the Commit on successful package completion check box is cleared, an open transaction is rolled back on package completion and pending updates are lost.
In the DTS object model, set the AutoCommitTransaction property of the package object. For more information, see AutoCommitTransaction Property (DTS).
- No steps failed.
- Transaction isolation level.
In this check box, you can select the level of locking used within transactions to protect the user from dirty reads, nonrepeatable reads, and phantom data. In order of increasing protection, available isolation levels are: Chaos, Read uncommitted, Read committed, Repeatable read, and Serializable. For more information, see Isolation Levels.
In the Workflow Properties dialog box of a step, on the Options tab, you can find:
- Join transaction if present.
If this check box is selected (and transactions are enabled), the step joins the package transaction. Updates accumulate until commit or rollback. If cleared, updates are carried out one at a time, as they are requested.
In the DTS object model, set the JoinTransactionIfPresent property of the step object. For more information, see JoinTransactionIfPresent Property (DTS).
- Rollback transaction on failure.
If this check box is selected, step failure triggers a rollback of the package transaction. Pending updates are discarded. If cleared, any updates remain in the transaction until a later commit or rollback.
In the DTS object model, set the RollbackFailure property of the step object. For more information, see RollbackFailure Property (DTS).
- Commit transaction on successful completion of this step.
If this check box is selected, successful step completion triggers a transaction commit. Pending updates are made permanent. If cleared, any updates remain in the transaction until a later commit or rollback.
In the DTS object model, set the CommitSuccess property of the step object. For more information, see CommitSuccess Property (DTS).