Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating