How To Use Transactions in a SQL Server Integration Services (SSIS) Package

[dhilipkumar]

SQL Server 2005 added two new options:

A variation on READ COMMITTED where you set READ_COMMITTED_SNAPHOT ON at the database level and any transaction that uses the READ COMMITTED isolation level will not acquire share locks and will not wait on any locks.  Rather, you will get the committed version of all rows at the time the SELECT statement begins.
A new isolation level called SNAPSHOT where you set ALLOW_SNAPSHOT_ISOLATION ON at the database level and any transaction that explicitly sets the transaction isolation level to snapshot will not acquire share locks and will not wait on any locks.  Rather, you will get the committed version of all rows at the time the transaction begins.
Both of the above SQL Server 2005 enhancements are made possible by maintaining committed versions of rows in tempdb (referred to as the version store).  When a read encounters a row that has been modified and not yet committed, it retrieves the appropriate latest committed row from the version store.  The maintenance and traversing of the version store is performed by SQL Server automatically; there are no code changes required.


Transactions in SSIS

Transaction support is built in to SSIS.  The TransactionOption property exists at the package level, container level (e.g. For Loop, Foreach Loop, Sequence, etc.), as well as just about any Control Flow task (e.g. Execute SQL task, Data Flow task, etc.).  TransactionOption can be set to one of the following:

# Required - if a transaction exists join it else start a new one
# Supported - if a transaction exists join it (this is the default)
# NotSupported - do not join an existing transaction
             The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running.  MSDTC also allows you to perform distributed transactions; e.g. updating a SQL Server database and an Oracle database in the same transaction.   If you execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, you will get an error message like the following:


'Error: 0xC001401A at Transaction: The SSIS Runtime has failed
to start the distributed transaction due to error 0x8004D01B
"The Transaction Manager is not available.". The DTC transaction
failed to start. This could occur because the MSDTC Service is not running.'

Note also that the SSIS package elements also have an IsolationLevel property with a default of Serializable.  As discussed above in the section on Transaction Isolation Levels, this setting impacts the duration of locks as well as whether shared locks are acquired.

SSIS Package Example:
Let's take a look at a sample SSIS package that we will use to demonstrate how to implement transactions at the package level and lock a table for the duration of the package's execution:

                               


The Test Initialization sequence container is used to create a test environment.   Two tables are created (TranQueue and TranQueueHistory) and a row is inserted into TranQueue.  This will allow us to simulate a process where the SSIS package  processes a group of rows inside of a transaction.  The TransactionOption setting for the Test Initialization sequence container is NotSupported since it only exists to create the test environment; i.e. we don't need any transaction support here which would rollback any successful steps in the event of a failure.

The Process sequence container has its TransactionOption set to Supported; since the package setting for TransactionOption is set to  Required, a transaction is created at the package level and the container will join that transaction.  Process TranQueue is an Execute SQL task that executes the following SQL command to simulate processing a group of rows in the TranQueue table:

Code Select Expand


DELETE TOP(10) dbo.TranQueue
OUTPUT DELETED.*
INTO dbo.TranQueueHistory
FROM dbo.TranQueue WITH (TABLOCKX)


The main points about this SQL command are:

# It deletes the first ten rows from the TranQueue table to simulate pulling them out for processing
# It uses the OUTPUT clause to insert the message column of each deleted row into the TranQueueHistory           table to simulate processing has completed and history is being updated
# It uses the TABLOCKX table hint to lock the TranQueue table

Code Select Expand

SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)

Code Select Expand

SELECT * FROM dbo.TranQueue