Handling Simple Transactions in SQL Server
Integration Services 2005
SQL Server Integration Services 2005 (SSIS) includes transaction handling straight out of the box. Using SSIS transactions you can:
- Execute atomic units of work and have all of them fail or
succeed together depending on the execution result of any of them.
- Rollback parallel data flows that must succeed or fail
together. For example you want to load data into some star schemas and if
one dimension table or fact table load fails you want to rollback all of
those parallel insertions.
- Control distributed transactions across multiple sources.
For example if a customer places an order you want to ensure that that
order is entered into both the order tracking system and fulfillment
system, or neither of them.
- Control asynchronous execution. For example you take a
message from a MSMQ queue and process it. If the processing of the message
fails you want to ensure that the message remains on the queue.
- Carry out multiple transactions in a simple package
A transaction can include many containers. It can include
containers within containers. A single transaction can even run over multiple
packages called from the “Execute Package” task or “Execute DTS 2000 Package”
task.
Note that SSIS transactions require Microsoft Distributed
Transaction Controller service to be running on the same machine that is
running SSIS.
Configuring Transactions
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.
Demonstration
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.
Test # | Package.TransactionOption | [Build Table] .TransactionOption | [INSERT 1] .TransactionOption | [INSERT Two] .TransactionOption | [INSERT 3] .TransactionOption | Results in destination table |
1 | NotSupported | NotSupported | Supported | Supported | Supported | 1, 3 |
2 | NotSupported | NotSupported | Required | Required | Supported | 1, 3 |
3 | NotSupported | NotSupported | Required | Required | Required | 1, 3 |
4 | Supported | NotSupported | Supported | Supported | Supported | 1, 3 |
5 | Supported | NotSupported | Required | Required | Supported | 1, 3 |
6 | Required | NotSupported | NotSupported | NotSupported | NotSupported | 1, 3 |
7 | Required | NotSupported | Supported | Supported | Supported | Empty |
8 | Required | NotSupported | Required | Required | Required | Empty |
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.
Summary
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.
Learn more about transactions by installing the attached
package and seeing how you can affect behaviour using the TransactionOption
property.
Installation of the attached package is very easy. Unpack
the attached zip file into a folder and execute DTSInstall.EXE. This deployment
utility is provided with the Business Intelligence Development Studio.
Jamie Thomson
Jamie is a Business Intelligence Consultant with Conchango in the UK. He has over 5 years experience of building decision support systems on the Microsoft platform. He is a frequent contributor to Darren Green's and Allen Mitchell's: