Handle Transactions In SSIS

Let see how to do Transations in SSIS Package.

I have Created one Simple Package in that I have used Four Execute Sql Task to Create the table and Insert data into that table.
Below I have attached the Screen Shot for more details.

Transaction_1

After Configured all four Execute Sql Task , Now we will run this package.
transaction_2

See my last Sql task is failed as excepted, Now we will check the Customers Table .
Transaction_3

Now Suppose here the Requirement is If the last SQL task is failed then only first Data should Insert (i.e Sql Insert Data 1) not Second one data.
To achieve this we need to set Transaction Property For Sql Insert data 2.
Below is the Screen Shot to Configure the Property

transaction_4

In the Above Screen Shot you can see that Sql Insert data 2 is set to Transaction Option=Supported so this task will Roll back

And For the other two task i.e Create Sql Table and Sql Insert Data 1 set TransactionOption Not Supported, So it will not Roll back whenever Error occured in SSIS Package.

transaction_5

After this we will run our Package again and see the data in Customer Table  .

Transaction_6

Only one row is inserted and Second Insert statement is Rolled Back .

Thanks 🙂

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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