locked
Managing the failure of a Foreach ADO Enumerator container - SSIS 2012 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    in a my SSIS 2012 pkg I'm using a Foreach ADO Enumerator container that reads an object variable in order to get an id value.

    This identifier is passed as an input parameter to an Execute SQL task to update an Oracle table: if this task fails the id is written on a SQL Server table. After the Execute SQL task execution, with success or failure, the flow go to another task in the container. When an error occurs for the update on Oracle table, each tasks inside the container are executed but the container fails and the loop ends.

    I'd like to complete the entire loop respect to the identifiers present in the object variable also if the update operation on Oracle table goes in error.

    Any suggests to me, please?

    Thanks

    Thursday, July 9, 2015 3:30 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Set System Variable Propogate Boolean to 'False' in the event handler of For each loop. 

    • Go to Event handler of For each loop task
    • Right click > variables [system] 
    • Search propogate and set it to false.

    This will stop for each loop to fail when inner task is failed.

    Hope this will help

    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    Thursday, July 9, 2015 3:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Anuj, thank for your reply, but I haven't any event handlers for the Foreach loop container. Do I need to create one by the Event Handlers tab?

    However, I've tried to use the MaximumErrorCount property of the container setting a value enoughly high.

    Thanks

    Thursday, July 9, 2015 4:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, before I need to create an OnError event and then set the Propagate system variable to false.

    But this setting avoids to have an error for the container; in order to continue the loop when the update fails I need to set a value enoughly high for the MaximumErrorCount property of the Foreach loop container.

    Thanks

    Thursday, July 9, 2015 5:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    i had the same issue looping number of excle file i don't know how much will fall in folder and  create/insert data in SQL table.

    suppose i have 500 file, if any one file in bad format my loop is break and i have start from beginning.

    i have solution instead of looping in foreach loop i loop in Script task, if catch execration my loop is not break and next file going to read from folder.

    Regards,

    Manish

    Friday, July 10, 2015 11:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Yes, please check these articles

    http://www.codeproject.com/Articles/384690/In-SSIS-how-to-continue-a-for-each-loop-container

    http://www.timmitchell.net/post/2013/08/05/continue-package-execution-after-error-in-ssis/

    Do not set maximum error count property when you use propagate, check below solution

    http://stackoverflow.com/questions/8972340/ssis-multiple-loops-propagate-not-working

    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    Friday, July 10, 2015 4:32 PM