SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager

SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager

Did you know....

...that you can run regular SQL Server transactions in SSIS over multiple tasks without requiring the use of Distributed Transaction Coordinator (DTC)? Well I didn't but thanks to that man again, Kirk Haselden, I do now.

The default behaviour of a Connection Manager is that every task has its own connection which is why they are so named and not simply named "Connection" as in DTS2000. The RetainSameConnection property on the OLE DB Connection Manager enables you to run multiple tasks against SQL Server all within 1 SQL Server transaction if its RetainSameConnection property equals TRUE. That means that you can BEGIN TRANSACTION in one Execute SQL Task and then choose to COMMIT or ROLLBACK in another one. You can even enlist data-flows in that transaction as well!

Allow me to demonstrate.

Here's my package.

In the first Execute SQL Task I create myself a table and begin a transaction.

The data-flow inserts some data into that table.

And the second Execute SQL Task rolls back the transaction.

If you check the table afterwards.....there's nothing in it!

Have a go - you can download the package from here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20050820RetainSameConnection.zip

Here was me thinking you could only run transactions either within the same Execute SQL Task or else using distributed transactions. There's just always something new to learn about this thing isn't there!!!