17

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 ?

Dinesh
  • 303
  • 2
  • 6
  • 19

1 Answers1

29

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

GShenanigan
  • 5,409
  • 5
  • 38
  • 48
  • Totally depends on the size of your transaction and how it's logged. If you're running a massive transaction that takes hours and it fails, you could still be looking at a long time rolling back. – GShenanigan Dec 17 '12 at 12:59
  • It's very help full for me Thank You so much. – Dinesh Dec 17 '12 at 13:04
  • 2
    WARNING !!! using transactions can cause problems. See my problem at - http://stackoverflow.com/questions/19869276/cannot-connect-to-database-after-putting-tasks-in-sequence-container – Steam Nov 09 '13 at 23:26
  • 2
    Although this is a 2005 article, it might still be applicable today - Beware of transactions. http://consultingblogs.emc.com/jamiethomson/archive/2005/01/31/SSIS_3A00_-Beware-of-transactions.aspx – Steam Nov 09 '13 at 23:27
  • I found another link which is very helpful http://sqlblogging.com/2011/10/17/transactions-in-ssis-with-example/ – Niraj Sep 24 '15 at 11:06