Validation and OnTaskFailed

Here's an interesting tidbit: an SSIS package can pass package level validation and fail task level validation. A quick way to jigger that up is to break a connection string. Why you would care is if you are using SSIS to report on errors. Where this gets interesting is that if you are looking for interesting situations via OnTaskFailed a validation error does not get caught by that event handler.

At my current engagement, in addition to using native SSIS logging, we capture runtime information and send an email with the pertinent bits included. We use a custom component but as the SSIS Madman displayed in his Error Handling Template, a script solution will work just as well. We notify on both success and failure and so the current solution has our custom component as the second to last step in the control flow as well as in the OnError task as we want to capture the ErrorDescription and ErrorCode. This has been working well, we receive notification when the packages run and when they fail. Well, we did run into one situation where the package failed to validate, some strange gremlin with the deploy but we corrected that by having SQL Agent send an alert on failure as well.

Fast forward seven months later and one of the developers is mucking about and creates a situation the current solution doesn't handle. He breaks a connection string which causes a validation error but it does not send an error notification. The OnError event is raised, it is populating the object we use for notification but the next step in the chain, the actual transmission of that message, does not take place. The package will error out so it knows that it's in a bad state but the OnTaskFailed event does not fire in the event that it fails to validate. Now, with our current solution of the SQL Server Agent watching return codes we handle the situation but I'm not satisfied with it. It should be an easy thing to hook the failed validation event and report on that.

Obviously if I'm posting this, I either haven't hit the dead simple solution or it is as painful as I've made it out to be.

It's been an interesting forray into SSIS to try and figure out what is going on behind the scenes. The first solution would be to simply send notification in the OnError task but that will result in an email for each error raised. One error results in four messages for the way we have our packages configured, your mileage may vary. What won't vary is the OnError getting called multiple times. Will messing with the Propogate, fail parent, etc flags help? Maybe, this is a work in progress. The best solution I've come up, with an assist from the madman, is to parse the error description and look for failed validation. When I encounter that, Dts.Events.FireError will very nicely help me fail the task and thus get me into the OnTaskFailed event handler. The problem I have with this solution is two-fold: it cannot exist in the base package's OnError event handler as when it fires the error event, that will be sent to one level higher. The second is that I don't know that the error description will always say "failed validation." What if it says, failed to validate or some other permutation.

There must be a hook somewhere.

What I can say doesn't work. OnPostValidate I thought maybe I'd get lucky and if it failed to validate, this event wouldn't fire. From there, I could just set a variable to indicate that it did validate and conditionally not send an email. No such luck, failure to validate does not cause the OnPostValidate event to fire.

This post has been in a draft state since May 2008. It hasn't been a pressing concern since then but at some point, I'll have to dig back in and see what I can learn. As it stands now, we have two error notification chains. The first is the package fails and OnTaskFailed fires and we receive email. The second is the invoking SQL Agent job is also listening for failure and that also sends notification.