Post reply

Fail loop but continue processing

  • January 13, 2011 at 8:49 am

    #142173

    I have a for each loop that is looping through a table of servers and calling a data flow task for each of the servers. If one of the data flow tasks fails, I want to report failure. However, the for each loop stops processing as soon as I return an error. I've tried several combinations of FailParentOnFailure and MaximumErrorCount but have not been able to get this to work. I could do this with a script task and a global variable but it seems like something that should be able to be done with normal flow processing. I appreciate any help I can get on this.

    Thanks,

    Brian.

  • January 13, 2011 at 8:59 am

    #1273113

    Brian

    Have you tried using an OnError event handler?

    John

  • January 13, 2011 at 9:12 am

    #1273118

    John Mitchell-245523 (1/13/2011)

    Brian

    Have you tried using an OnError event handler?

    John

    What did you have in mind? I could use this to set the global variable instead of a script task. That would be cleaner. I was hoping to avoid that type of processing if possible though. I guess with my days as a C programmer I hate global variables.:-)

  • January 13, 2011 at 9:30 am

    #1273133

    You can force the execution result of the data flow to succeed, so your loop would always continue.

    However, this raises another problem: how to detect failure? If you can do it in the data flow somehow, that could be an option.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 13, 2011 at 11:15 am

    #1273200

    Koen (da-zero) (1/13/2011)

    You can force the execution result of the data flow to succeed, so your loop would always continue.

    However, this raises another problem: how to detect failure? If you can do it in the data flow somehow, that could be an option.

    I can force the excution to continue by setting the MaximumErrorCount on the ForEach Loop to 0, and leave the FailParentOnFailure and FailPackageOnFailure of the Data Flow Task to False. Unfortunately, the package reports successful execution. I'm going to go with the OnError event and a global variable. Then I can evaluate the variable afterwards and fail the package... It's not the cleanest way to do it but it will get the job done.

  • January 14, 2011 at 2:29 pm

    #1273804

    Brian Carlson (1/13/2011)

    Koen (da-zero) (1/13/2011)

    You can force the execution result of the data flow to succeed, so your loop would always continue.

    However, this raises another problem: how to detect failure? If you can do it in the data flow somehow, that could be an option.

    I can force the excution to continue by setting the MaximumErrorCount on the ForEach Loop to 0, and leave the FailParentOnFailure and FailPackageOnFailure of the Data Flow Task to False. Unfortunately, the package reports successful execution. I'm going to go with the OnError event and a global variable. Then I can evaluate the variable afterwards and fail the package... It's not the cleanest way to do it but it will get the job done.

    You can implement a script, which is connected with failure constraint to your data flow. Whenever the data flow fails, increment the variable value. Essentially the variable will contain how many times your data flow task failed. Then after you finish executing the loop, setup a constraint combined with expression which checks if the counter variable is greater than 0. If true, then you fail your package.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply