How to roll back transactions in SSIS (SQL Server Integration services)

In SQL Server Database programming we use begin transaction and roll back transaction for transactions, Similarly SSIS also will support transaction in package execution.

The below example will shows how to roll back the entire transaction in case of transaction failure.

In order to show this I used two execute SQL tasks

One for creating a table and second one insert into the newly created table.

In First execute SQL Task

create table testrollback(id int,transname varchar(100))

 In Second execute SQL Task I have provided the following SQL commands

insert into [dbo].[testrollback] values (123,'Insert')

insert into [dbo].[testrollback] values ('fail','Insert')

By default the transaction support option for ExecuteSQLtask or package is supported. There are three options available for package in SSIS.

        *   Not Supported

        *  Supported

        *  Required

When transaction option set to require the “Distributed transaction coordinator” service should be started. Otherwise you will get the following error while running the package or individual task.

Error: 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.

Once the distributed transaction started you can use transaction option required so that if any failures in the execution of task in control flow then it will roll back the entire transaction.

First we will check the transaction option Supported at package level and execute the package.

The First task is running successfully and the second task failure because of conversion error. In this scenario the create table statement in first task is committed and in second task first statement executed successfully only second statement will fail.

verify the script the changes in database

For Supported Transaction option it does not roll-back the changes in package execution. 

Then let us try to change the transaction option of package to required

Drop the testrollback table from database and execute the package.

Execute the package once again and verify the changes in database

This time package failed but its roll-back the commands in first execute SQLtask. The table was not created in the destination table.