Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Transactions in SQL Server 2005 Integration Services

By Jamie Thomson, (first published: 2005/01/03)

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 .

Total article views: 40952 | Views in the last 30 days: 37
 
Related Articles
FORUM

IsolationLevel = Snapshot, TransactionOption = Required

We are trying to use the snapshot option for the transaction isolation level

FORUM

Check Statement aborted. Database contains deferred transactions.

Please help me, Check statement aborted. Database contains deferred transactions.

FORUM

TransactionOption

Several tasks in my SSIS package use stored procedures. Inside the SP Ive used a transaction that r...

FORUM

Explicit Transactions Vs TransactionOption in SSIS

Hi Friends, I am going to implement Transaction in a package. I thought of using the Transaction ...

FORUM

Issue with TransactionOption of SSIS

Hi All, I created a SSIS package which contains two "Execute SQL Tasks" components - one inserts/...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones