Multiple Transactions
It is possible for an Integration Services package to include multiple unrelated transactions. This happens when a package contains a hierarchy of nested containers. and one of those containers does not support transactions while the remaining containers require transactions. The nested containers that require transactions will start separate transactions of their own. These transactions commit or roll back in order from the innermost task in the hierarchy of nested containers to the package. However, after the transaction of an inner container commits, that transaction does not roll back if an outer transaction is aborted.
For example, a package has a Sequence container that holds two Foreach Loop containers, and each of those Foreach Loop containers includes two Execute SQL tasks. The following diagram shows this example package.
.gif "Implementation of multiple transactions")
In this example package, the TransactionOption properties of the Sequence container, the Foreach Loop containers, and the Execute SQL tasks are set as follows:
The TransactionOption property of the Sequence container is set to Required.
The TransactionOption properties of the Foreach Loop containers are set to NotSupported.
The TransactionOption properties of the Execute SQL tasks are set to Required.
With these settings for the TransactionOption properties, there will be five unrelated transactions in the example package. The Sequence container starts one transaction, and the four Execute SQL tasks each start their own transaction. Furthermore, the transactions that the Execute SQL tasks start would not roll back if the transaction on the Sequence task was aborted.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration ServicesSee Also