SSIS transactions are controlled by the TransactionOption property.
The TransactionOption property applies to every container including the TaskHost container.
It has 3 enumerated values:
- NotSupported – The container will not execute within a transaction, even if a transaction has been started by a parent container.
- Supported – If a transaction was started in a parent container the container will enlist in the transaction
- Required – A new transaction will be started from the container if a transaction has not been started by a parent container. If a transaction has been started by a parent container this setting exhibits the same behaviour as Supported.
Hence, if I want all containers in a package to succeed or fail together I should set TransactionOption=Required on the package container and set TransactionOption=Supported on all the child containers.
Note that a container does not enlist in a transaction unless a transaction has been started in a parent container. It cannot enlist in a transaction started by a sibling container.
In order to demonstrate the above rules an example SSIS package is provided along with this article. The picture below illustrates this package.
There are no data flows in this package, only “Execute SQL” tasks. Remember that each task implicitly executes inside a TaskHost container. All these tasks work against a connection that you should set up to point to tempdb on a SQL Server instance. You will be prompted to select your server when you install the package. The tasks carry out the following operations:
- Build Table – Builds a table into which data is to be inserted. If the table already exists it will be dropped. The table has 1 column of type integer.
- INSERT 1 – Attempts to insert the integer literal ‘1’ into the table.
- INSERT Two – Attempts to insert the string literal ‘Two’ into the table. This insertion will fail.
- INSERT 3 – Attempts to insert the integer literal ‘3’ into the table.
The precedence constraints are all set to “Completion” which means that all tasks will get executed regardless of whether the previous task succeeds or fails.
Tests have been run using this package to demonstrate the behaviour of the TransactionOption property. Each test had a different combination of this setting across the package’s container hierarchy. The results of the tests are shown below.
The value of [Build Table].TransactionOption was always set to NotSupported so that each test worked on an empty table.
- We can see from tests 1 and 4 that the TransactionOption of ‘Supported’ on the insertion task containers did not have a discernible affect on the transaction behaviour of the package. This is because no parent container started a transaction in which the insertion task containers could enlist. There was no difference in behaviour when setting Package.TransactionOption=Supported or Package.TransactionOption=NotSupported because there was no transaction created in a parent package in which the package could enlist.
- Tests 2 and 5 show us that if a container has TransactionOption=Required, and no transaction has been started in a parent container, that container will start a transaction of its own. That transaction will exist for the life of that container, including any child containers if they do not have TransactionOption=NotSupported.
- In test 3 a new transaction was started by each of the insertion task containers hence the failure of [INSERT Two] had no bearing on the other insertion task containers.
- In test 6 a transaction was started by the package container but none of the insertion task containers enlisted in the transaction because they had TransactionOption=NotSupported.
- In test 7 a transaction was started by the package container and all the insertion task containers enlisted in that transaction. They all rolled back because one of the tasks in the transaction failed. It is interesting to note that in the IS Designer GUI [INSERT 3] was red which indicates failure, even though it is a valid operation.
- Test 8 resulted in a transaction being started by the package container in which all the insertion task containers were enlisted. This resulted in all the insertions being rolled back.
- Clearly (from test 7 and test 8) whenever a transaction is started in a parent container a container will enlist in that transaction if it has TransactionOption=Supported or TransactionOption=Required. A container will not start a new transaction of its own if a transaction has been started in a parent container.
SSIS has a flexible approach to managing distributed transactions that is controlled using the TransactionOption property of a container.
From the tests above we can deduce the following guidelines:
- If a container starts a transaction all child containers will enlist in that transaction if TransactionOption=Supported or TransactionOption=Required.
- A container will only start a new transaction if a transaction was not started in a parent container.
- A transaction completes when the container that started the transaction has finished executing. A sibling container cannot join that same transaction.