Transactions in SQL Server 2005 Integration Services

,

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:

  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:

and .

Rate

4.69 (13)

Share

Share

Rate

4.69 (13)