Generic OnError Event Handler in SSIS

Josh,

So you're needing to custom log errors in SSIS, and naturally you seek to utilize the "OnError" event handler. You don't want to have to implement an event handler on each task, so you create one at the package level. You place an "Execute SQL Task" in the event handler and set it up to log the error. Great!

You then test with an error, and the event handler runs, but when you go to check the custom log in SQL Server, you see multiple entries for the same error! There has to be a way to ensure only one error gets logged...

Side Note: Starting with SSIS 2012, packages ran from the Integration Services Catalog already have logging. If you don't have a specific need for custom logging, then use that!

So the solution you came up with relates to a similar issue you've seen solved before. That issue is multiple firings when trying to log package starts/completes with the "OnPreExecute"/"OnPostExecute" event handlers. The solution there was to add a "dummy" Sequence Container before the logging tasks then using an expression for the constraint. (Example)

This won't work for the OnError event handler because the SourceID for the error is the executable that had the error in it, which will only be the PackageID if the error occurred at the package level. So, your error never gets logged.

If we could identify the scenario where the error's SourceID was the same as the current executable's, then we could guarantee it only gets logged once. There doesn't appear to be a system variable for that, so let's create a user defined one:

We want this variable to always contain the current executable's id, so to accomplish this, we'll utilize the "OnPreExecute" event handler. Add an "Expression Task" to the event handler and call it something like "Set CurrentSourceID":

Set the expression to "@[User::CurrentSourceID] =  @[System::SourceID]". So each time the "OnPreExecute" event handler fires, we'll have the id for that executable. Now we can go to our "OnError" event handler and update our constraint expression:

Setting the expression to "@[System::SourceID] ==  @[User::CurrentSourceID]" now ensures the error is logged only where the error existed!