Hi experts, I have a Foreach loop and some tasks inside(DataFlow, Exec Sql Task). 1. Foreach Loop: Loop through all XML files in a folder 2. DataFlow: Process each XML and load into 10 tables from the same XML 3. Execute SQL Task: Update the status of the XML file to Succeeded if all the tables are successfully populated in the prev step. My question is, if any file caused error or any issues, I dont want to fail the package. I want to continue with next XML file in the loop without updating the status(Exec SQL Task). Logging will write the entry about the error. Note: As there are 10 tables populated from each XML file, some tables might be successfully loaded. If any table fails, I want to rollback all other tables in the dataflow for the current XML file whatever the rows succeeded for the current XML file. How can I achieve this?

Increase the MaximumErrorCount of the Container and set the FailParentOnFailure to false.

This is how I would do it: before the dataflow, I would place an Execute SQL Task with the statement BEGIN TRAN. After the dataflow, I would place another Execute SQL Task with the statement COMMIT and connect it with the green arrow. Then I would add yet another Execute SQL Task after the dataflow, but now with the ROLLBACK TRAN statement and connect it to the dataflow using the red arrow. Now; I'm not sure the For Each Loop will go to the next iteration when a failure happens. I would think so, since you are handling the error with the Rollback Execute SQL Task. Test it out and let us know if it works. edit: oh yeah, in order to make this work, you need to use only one connection manager and set the property RetainSameConnection to TRUE. MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Hi Koen Verbeeck, Thanks for the reply. edit: oh yeah, in order to make this work, you need to use only one connection manager and set the property RetainSameConnection to TRUE. I have different connection managers in the package. Is it ok to RetainSameConnection to TRUE. Then I would add yet another Execute SQL Task after the dataflow, but now with the ROLLBACK TRAN statement and connect it to the dataflow using the red arrow And, if DataFlow task fails, the package will be stopped right? How can it comes to next step in the forloop?

edit: oh yeah, in order to make this work, you need to use only one connection manager and set the property RetainSameConnection to TRUE. I have different connection managers in the package. Is it ok to RetainSameConnection to TRUE. Ah no. The transaction with Execute SQL Statements only works over one single connection. You can use transactions within SSIS by setting the TransactionOption property on the dataflow to REQUIRED. This method uses the MSDTC (Microsoft Distributed Transaction Coordinator), so make sure it is running in your organization. Then I would add yet another Execute SQL Task after the dataflow, but now with the ROLLBACK TRAN statement and connect it to the dataflow using the red arrow And, if DataFlow task fails, the package will be stopped right? How can it comes to next step in the forloop? I'm not sure about that, you'll need to test that. If it does stop, you need to increase the MaximumErrorCount on the containers, as Christa suggested.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...