4
votes

I'm new to SSIS. I m trying to load data from excel to sql server. Each sheet in excel should go into separate table in sql server so I created a sequence container and inside that I have placed data flow tasks. Each data flow task contains logic to move data from one excel sheet to table in sql server. So problem is if one data flow task fails then I was expecting whole package will fail and rollback the changes done by other data flow tasks. But its not doing that. Other data flow tasks are getting committed into sql server.

I have set the property "Failpackageonfailure" to true for sequence container and individual data flow tasks. Also set the transaction as "Required" but no change in results.

Please help!

Thanks in advance

Sai

1
Found solution!!. Thought of posting the solution I found. I also need to set the package level TransactionOption as "Required". Then it worked. - Sai

1 Answers

0
votes

You could have also set Required to a newly created sequence container with the control flow items inside of it (in case your package does other tasks that do not behave the same way).