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.

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:

1. If a container starts a transaction all child containers

   will enlist in that transaction if TransactionOption=Supported or

   TransactionOption=Required.

2. A container will only start a new transaction if a

   transaction was not started in a parent container.

3. 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.

Download the sample

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: