Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to transaction rollback in ssis?

Tags:

ssis

I have data import to Production server. In my package cleanse raw tables before data load. If any case package failed . How to rollback raw tables data in SSIS ?

like image 646
Dinesh Avatar asked Nov 29 '12 13:11

Dinesh


People also ask

How do you rollback a transaction?

You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction. This does not include changes made to local variables or table variables. These are not erased by this statement.

Can we rollback committed transaction?

COMMIT permanently saves the changes made by the current transaction. ROLLBACK undo the changes made by the current transaction. 2. The transaction can not undo changes after COMMIT execution.

How do I use rollback?

The ROLLBACK Command This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued. Following is an example, which would delete those records from the table which have the age = 25 and then ROLLBACK the changes in the database.


1 Answers

By using Sequence Containers (Sequence, For Loop, ForEach Loop) in SSIS, you can make use of a property called TransactionOption. This allows you to specify the transactional behaviour of the tasks in your package, and the package itself to rollback if any tasks fail.

For example, if you stick your tasks in a Sequence Container and set TransactionOption=Required, and configure all Tasks within the Container to TransactionOption=Supported, they will all join the transaction started in the Sequence Container, and if any fail, the transaction will be rolled back.

You can read an explanation of the TransactionOption property here and/or follow the walkthrough here to see how to implement this.

EDIT: Another good walkthrough (with screenshots) here

like image 200
GShenanigan Avatar answered Sep 22 '22 19:09

GShenanigan