Suppose you are working on SSIS package that contains more than one Execute SQL tasks and no other tasks. You want to rollback all the results of Execute SQL tasks if one of them fails. The best way to achieve this will be to set TransactionOption property of the package to Required.
Let us see this in action:
Step 1: In SQL Server Management studio create a test table called MyTable
Step 2: Assign values to MyTable for test purpose.
Step 3: Validate data in the table.
Step 4: Now In SSDT or BIDS create a simple package that has four Execute SQL Tasks.
Step 5: Add the following code to the script tasks.
Task1:
UPDATE MyTable SET Name =N'Z' WHERE ID=1; |
Task2:
UPDATE MyTable SET Name =N'Y' WHERE ID=2; |
Task 3:
SELECT 1/0 |
Task 4:
UPDATE MyTable SET Name =N'X' WHERE ID=3; |
Task 1 preview
Task 3 preview
Step 6: Leave the default value TransactionOption =Support and execute the package.
It is not a surprise why the package failed it is for the reason division by zero.
Step 7: Now let’s go back to Management Studio and check what change has be made by the package.
As you can see ID 1 and 2 where changed because they were changes before task 3 that causes the package to fail but ID 3 is not change because it was after task 3 and the package failed before it got a change to be executed.
Step 8: Now let’s remove all data from MyTable then reassign the previous values.
After you execute the previous script repite Step 2
Step 9: Now let us go back to management studio change the TransactionOption property to Required then rerun the package once again.
Property
Execution result
Step 10: Let’s go back to Management studio for one last time and check what changes where made.
As you can see here all changes made by any of the preceding packages before the failed task rolled back.
For more information checkout this reference materials
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx
