SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using Database Transactions across multiple SSIS Tasks


If you experiment at all with transactions that are built into SSIS you will discover that they are highly flawed.  For example, if you have a transaction running on an entire package some tasks may not rollback on a failure.  A file system task is one of the major culprits that on a failure will not rollback the file operation it is performing.  I wish I was writing to give you a solution to that problem today but I’m actually writing to show how you could use a typical database transaction across multiple tasks in SSIS.

To demonstrate this I designed the package below.  The #1 Execute SQL Task starts the transaction and #3 commits it.  It sounds good but when I run this package the value of 2 is still inserted into my table.


To make this work how you would think it should you simply need to change one property in the connection manager properties.  Select the connection manager used in these tasks and change the property called RetainSameConnection to true.


When you run the package again no values are inserted.  This is exactly what I wanted.

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by Anil Kumar Kubireddi on 8 March 2010

nice one...help full.

Posted by robink on 8 March 2010

Really a nice one.

Posted by Ritesh on 9 March 2010

Hi Devin

Thx for this update, but when I try this in one of our SSIS which is used to convert Access db into SQL. It does not work as you have mentioned.For example,I have SSIS which has following task define

A->B->C->D->E. All this task insert data into SQL from Access. Due to error SSIS failed at task D, but it still insert data into tables A,B,C which should have been rollback.

I have connection as below:

connAbc pointing to ABC db on SQL

connXYZ pointing to XYZ db on SQL

connLMN pointing to LMN AccessDb

The above task insert data into ABC DB on SQL server. I configure "reatainconnection" = true for connABC and all of the task's "transactionsupport" = supported, infact I try with "required" as well.

Can you help me figure out whats wrong here. Should you need any clarification pl let me know.

Thanks in advance


Posted by Ritesh on 9 March 2010

in addtion to above I am not using any loop container

Posted by knight_devin@hotmail.com on 9 March 2010
Leave a Comment

Please register or log in to leave a comment.