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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s