Preventing SQL Agent Jobs calling SSIS reporting failure

[James Beresford]

The ETL Control system I have in place has a series of parent and child packages, the lowest child of which can possibly and legitimately fail, and the Control flows I have in place gracefully handle that possibility, logging the failures back to Audit / Control database.

However, whenever a task fails, the error gets reported up the Parent / Child package chain all the way to the SQL Agent Job running the control framework and the SQL Agent job itself reports failure. This gives the wrong impression to anyone viewing the job history – it looks like the job has failed when in fact it has run just fine, handling the problems it encountered.

Rushabh Mehta presents an elegant solution in this post: Gracefully Handing Task Error in SSIS Package. His solution is to create an OnError Event Handler for the task that can possibly fail, and in that Event Handler find the System Variable Propagate and set its value to False. What the Propagate property does is decide whether Error messages are propagated up through the package. By setting it to false, you tell the package not to send Error messages up the execution chain. The Event Handler itself doesn’t have to do anything, it just has to exist and have the Propagate property set to False. This way the task can fail, not cause the SQL Agent Job to fail, but still allow handling of Errors in the Control flow through the normal use of Precendence constraints.

Unfortunately if you have a chain of Parent – Child packages this doesn’t quite work. If you try to apply this technique to an Execute Package task, it doesn’t work because if the Child package fails at any point – even if you prevent errors propagating up from inside the Child package, the Parent package will still raise errors and report failure. What I have found is that you have to have an Event Handler that prevents the propagation of errors at each step in the chain of packages that call that child package – so if you have Parent > Parent > Parent > Child, stopping propagation of errors in the Child isn’t enough to get the SQL Agent Job to believe it succeeded, you need to stop error propagation at each Execute Package task. I have a set of sample packages demonstrating this process here. It appears the only way to ignore errors at the bottom level is to ignore errors at every level, which is not a good approach.

As far as I can tell this is a bug, so I have reported it on connect.

If anyone has a solution to this problem I would be grateful to hear it – I don’t want to disable errors in every called package, just stop them propagating from the Execute Package Task at the bottom of the chain.



Get More from the original blog...