Why won't my failure flow initiate when SSIS sequence container fails?
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
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.
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.
Check the sequence container property if FailPackageOnFailure=true it will not fire "failure precedence constraint".
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/