For each loop error handling RRS feed

  • Question

  • I have SQL task in the for each loop..

    I would like to continue ( go to next row) to process even though one of rows that I am looping thru got faild..

    How can I prevent from failing the whole package and keep going unitil it loops thru the whole records  in For each loop?



    Wednesday, October 5, 2011 10:27 PM


All replies

  • 1) Set the "ForceExecutionResult" property on the Execute SQL Task to "Success" or "Completion" so it can never fail.


    2) Create an empty "onError" Error handler on the Execute SQL Task and set teh value of the System variable "Propagate" to False, so the loop ignores any failures.

    James Beresford @ www.bimonkey.com
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
    Wednesday, October 5, 2011 11:22 PM
  • thanks.. but how can I still save the error log to a table so I can take a look at later on?



    Thursday, October 6, 2011 3:47 AM
  • also you can set MaximumErrorCount property of the package to an appropriate value.
    • Proposed as answer by Eileen Zhao Tuesday, October 11, 2011 9:52 AM
    • Marked as answer by Eileen Zhao Wednesday, October 12, 2011 2:32 AM
    Thursday, October 6, 2011 5:23 AM