5

In my SSIS package I have several data flow tasks.

Is there a way in SSIS which says if the F0101Z2 task completes but the F03012Z1 fails rollback what was passed in the F0101Z2 step?

My SSIS package

Any guidance would be appreciated.

Thanks

Jonno Lord
  • 314
  • 1
  • 3
  • 14

2 Answers2

2

Set TransactionOption=Required on Package Level, and configure all Tasks to TransactionOption=Supported(by default Supported). They will all join the transaction, and if any fail, the transaction will be rolled back. Note: First Make Sure that MSDTC(MS Distributed Transaction Co-coordinator) is enabled in the Control Panel--->Admin tool-->Services.

user2771704
  • 5,994
  • 6
  • 37
  • 38
Rajashekhar
  • 75
  • 11
  • If you find that the process ends up getting hung up after you make this switch, please see: http://stackoverflow.com/questions/15508594/ssis-data-flow-task-hangs-on-excecution-of-pre-excecute-phase You may just need to change a option or two on some of your components and connections – Sam Mar 13 '17 at 20:14
0

Just study these Links and you will be able to solve your Problem.

how to use transactions in sql server integration services ssis/

SSIS transaction management MSSQL

How to transaction rollback in ssis?

Community
  • 1
  • 1
Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
  • thanks for the links, very useful. my next problem is I'm sending data from ms sql to oracle using global temp tables so i need to set the connection managers to retain same connection. this seems to cause the sequence container to fail if i do this and if i don't retain the same connection further steps fail. looks as if sequence container isn't for me on this occasion. – Jonno Lord Apr 25 '13 at 13:28