Preventing SQL Agent Jobs calling SSIS reporting failure

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.

Connect Update 18 Aug 2010: 

Looks like this won’t get fixed as it currently stands, the response from Microsoft was to Close it as “By Design”:

The “propagate” value only controls if an event should be propagated up
repeatedly. It doesn’t affect if the event is propagated to the client.
For child task, client is the parent package.

We looked the issue and decided that we cannot change the current
behavior. To address the issue properly, we will consider introducing
option like “Propagate to client” in the future.

 This does actually make sense architecturally – so we will just have to hope that the “Propagate to client” property becomes a reality.

Read More

Event Handlers and Checkpoints

I was recently playing with creating a Package based logging and auditing system using Event Handlers. The principle being that I would use the firing of the Event Handlers to drive Inserts into a database table that could log package events. In doing so I came up against one problem I knew about and could work around and one that I didn’t that frustrated my plans. I have created a sample package which demonstrates both issues here

Why do Event Handlers fire multiple times?

Event handlers fire multiple times because they fire both at the package and control flow component level. If you run the sample package you will note that the OnPreExecute event fires 3 times – once for the package and once for each of the two steps on the control flow. If you want to control the Event Handler so that it only fires once at the package level, you need to precede any activity with a constraint that means it will only execute if System::SourceName = System::PackageName. I usually do this by just putting in a Script Task with no code as the preceding task on which to base the constraint – it will always succeed and doesn’t slow things down.

Which Event Handlers fire when resuming from a Checkpoint?

This one’s easy – none of them. This caught me slightly off guard, but the Checkpoint file resumes the package from the exact point at which it failed, and does not re-run already fired Event Handlers – if you run the sample package twice (once to fail, once to resume from the checkpoint) you’ll notice that first time you run it, my annnoying message boxes pop up showing all sorts of events firing. The second time you run it, it resumes from the precise point of failure – no validation, pre execution or even error events fire at all. Which means if, like me,  you want to log a package restarting from a checkpoint by using an event handler – you can’t!

Read More