Managing Transaction in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

SSIS supports transactions as you usually use in TSQL, there are 2 kinds of transactions in SSIS :

1. Distributed Transaction

This transaction uses 2 phase commit transaction with Distributed Transaction Coordinator (DTC) service. Make sure this service runs before you run your package that uses the service. You don’t have to explicitly state begin tran…commit/rollback tran, because they are handled automatically by DTC service

2. Native SQL Server engine transaction

This transaction uses explicit BEGIN TRAN/COMMIT/ROLLBACK TRAN, explicitly in Execute SQL Task component. You must setting up the involved connection manager’s RetainSameConnection property from false to true.

For first alternative, there are 3 options for TransactionOption properties :

1. NotSupported

The component will not join to transaction scope.

2. Supported

The component will join parent’s transaction scope if exists

3. Required

The component will join parent’s transaction scope if exists, otherwise it will start new component.

Let’s start by create 3 Execute SQL Tasks that refers to same connection manager. Point to AdventureWorks, and you can create dummy table called test with 1 character column. Put there 3 each of these 3 statements into each of Execute SQL Tasks.

Create table dbo.dummy (cola varchar(10) not null);

Insert into dbo.dummy values (‘a’);

Drop table dbo.dummy;

Set up property TransactionOption from Supported (default) to Required for package level. Make sure DTC service already runs, otherwise, the package will fail.

Run your package, and there will be a table created and populated in AdventureWorks database.

For second alternative, you don’t have dependency to DTC service. DTC service is used when you want span transaction over more than 1 connection, tasks even more than 1 package, otherwise, it is simple to use native transaction TSQL statement.