TransactionOptions in SSIS package

· Uncategorized
Authors

Recently I performed some tests on transaction behavior in SSIS; these are my findings

Current situation
We use a TSQL BEGIN/COMMIT TRAN within a SQL tasks to define the boundaries of a transaction within a package. All SQL tasks within these boundaries will succeed or fail as one unit of work if you define the RetainSameConnection option for the connection manager to TRUE
Problem
A data flow task that resides within a BEGIN/COMMIT TRAN flow, runs under a separate transaction. I.e. if a SQL Task after the data-flow task fails, the data generated by the data flow task will not be rolled back. This is kind of confusing.
Solution
Use TransactionOptions in SSIS package.

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

%d bloggers like this: