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.


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

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

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


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.


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


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

Thanks 🙂


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s