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 .