Why won't my failure flow initiate when SSIS sequence container fails?

13
votes

I have some SSIS sequence containers, and if any of the tasks in them fail I want to direct the flow to a SQL task that cleans everything up so I can address the issue and run it again without having duplicate data. My problem is that I can't get the flow directed into the SQL task even when something in one of the sequence containers fails. Seems like this should be the default behavior, but there must be some setting I'm mising. I played with setting FailParentOnFailure, but all this does is stops the rest of the package from running. I would insert an image to help with understanding what I have set up, but guess my reputation isn't good enough yet. :P

4
ssiscontainerssequenceconstraintsoperator-precedence
You can always link to an offsite host like twitpic or another provider like that. Otherwise, describe how you are attempting error handling beyond fiddling with the FailParentOnFailure setting. – billinkc
Basically all I have is a few data flow tasks inside a sequence container. If any of those data flow tasks fail (if say the text file I'm trying to load to the database isn't available) then I want the sequence container to then follow the 'failure' precedence constraint instead of the 'success' one. But instead of doing either it just kills the whole package. If it doesn't follow the 'failure' precedence constraint by default on an error then I don't understand what the point of having a failure precedence constraint is. – BeachBum

4 Answers

24
votes

I think in this definitely one instance where a picture would have helped others answer my question. I had 3 sequence containers all pointed to the same SQL task if they failed. I wanted the SQL task to run if ANY of the sequence containers had an error, so the precedence constraints had to be set to a logical OR, otherwise it would never run unless all 3 happened to fail. Changed it to OR and it worked just like I wanted.

7
votes

Make sure that you click Edit on the Constraints and change the last portion for Multiple Constraints to OR so that if any of them fail they execute the task, otherwise it will wait for all of them to fail.

4
votes

Check the sequence container property if FailPackageOnFailure=true it will not fire "failure precedence constraint".

1
votes

Without more details on your Sequence Containers it is hard to be sure about the problem.

I would try to check the Propagate system variable of your containers. Whenever you have a task that can fail and you dont want your whole package to stop, make sure you do one of the following:

  • Go to Event Handlers and do something on the OnError event to treat the failure or
  • Set the Propagate System Variable of that task to False

I would provide some screenshots, but i do not have enough rep for that. Instead i leave you with some links with more information about this subject:

Similar to your question: http://sqlserverselect.blogspot.com/2010/12/ssis-foreach-loop-container-continue-on.html

Detailed info about event propagation: http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/