It doesn’t matter how long you use a tool, you will find that there is always functionality within that tool that you didn’t know existed. Such a thing happened to me this week whilst using SSIS within which i seem to spend a good portion of my working life. Now you should understand that I am talking about SSIS 2008 but the functionality is similarly exposed within later versions of the engine too. Let’s set the scene… I have a job for a long standing client which upon any task failure occuring writes a record to the audit table to indicate that the package failed to complete. Imagine my surprise when the same jobs started to write both failure AND success results to this audit table.
Odd.
A little bit of digging through the event log and I managed to isolate the issue. It seems that the underlying problem (which is not really that important other than for a bit of background) is caused by a sometimes not responsive SMTP server. The sometime failing transformation is supposed to send out an overview email for diagnostic reasons. When this transformation was encountering issues with the SMTP server no error was being raised to terminate the package execution (althought there undoubtedtly was an error) but the package level ‘OnTaskFailed’ event was instead being triggered. The triggering of this event caused a write of the ‘Failed’ audit log record. It seems that because the SMTP operation itself is embedded within a ‘OnPreExecute’ handler of another data flow the system deals with this error very differently in that it does not force the entire package to fail. For this reason execution continued despite the ‘error’ and the process would often run to completion. Quite often this would not be an issue but in this instance it was because an external system was looking for any failures and basically doing the electronic equivalent of ‘dobbing me in’. Moving on from this I also realised that a secondary email that I would also send to indicate other diagnostic information could occasionally fail BUT….. when that failed it would fail the whole package. The difference with this second job is that the workflow for this falls directly within the main package flow (not inside an event handler) .
So I sat…. and I thought about this and how exactly I wanted to cope with these errors. I realised that in reality whilst I would like the email(s) to be sent if it doesnt get sent its not such a big deal and so really I should be treating this as a non critical error. Failing the entire package, as the second email task on occasion did, was definitley not the correct behaviour. Thinking about it neither was the behaviour that the first email task was exhibiting; Yes it didn’t terminate the package (which is good) but it did throw an onTaskFailed event (which is bad in this instance). I needed the system to ignore errors raised by these, BUT ONLY THESE, tasks. So I thunk and I googled and I googled and I thunk and then came across the Package Explorer window which I’m ashamed to say until this point I had never used; I didn’t realise that it could be used to set variables and react in a script like manner to all of your executable jobs. This in association with a system variable called ‘System:Propagation’ gave me all of the tools I need to be able to deal with these SMTP issues correctly.
This is how I approached the whole solution, firstly I needed to ensure that my second email invokation behaved in a consistent manner to the first and for this I needed to move the responsible data tasks into an ‘onPostExecute’ event handler of a much later transformation. Let me detail this using my own ‘BeginStaging’ Execute SQL Task that you see defined here:
As you can see I click on the Event Handlers tab and under the ‘Executables’ I search for that ‘Begin Staging’ task.
Once I have selected the BeginStaging executable I then select the ‘OnPostExecute’ event handler which has not yet been defined. Upon selecting this you should see the following which will allow you to create the handler:-
So I ‘Click Here’ as instructed and after a pregnant pause a handler is created. Once the handler has been created it is just like any other design surface and we can add our ‘Send Email Task’ to this surface.
Importantly, embedding it within this handler will prevent a failing SMTP connection from failing the whole package immediately (because at the top level the handler will ‘handle the error itself’) but as things stand it will still trigger a ‘OnTaskFailed’ at the package level which is where my main error handling is located and also the main process will return ‘DTSER_FAILURE’. We thus first need to ensure that we can override the default behaviours of the ‘OnTaskFailed’ and ‘OnError’ events for this ‘Send Email’ task. We must thus ensure that this task has both of these events defined in the Event Handlers tab for a start:
With both of these handlers now created the groundwork has been laid for dealing with these issues at runtime, In order to prevent our errors failing our package we now need to look at these events within Package Explorer tab (note that you may need to close and reopen your pacakge in order to refresh… ). We start by finding the Executables and then within this list we need to look for our parent component ‘Begin Staging’, underlying this will be more executables. In this instance we thus want to find our Email task (which is the task that is failing) and in turn expand the event handlers to expose both the ‘OnError’ and OnTaskFailed events handlers which is where we will handle any of the issues that arise from that component.
In these two instances we know that we want to inform the system that we have handled both the error and the task failed events so that neither get ‘bubbled up’ and trigger the package level event, we thus open the ‘Variables’ underlying the two event handlers and select the ‘System:Propagate’ and/or ‘Propagate’ variable. Please note that that there will be multiple versions (different scopes) which just for certainty I handled both of. In reality you should just be able to handle the lowest level ones.
Using the property inspector we can then change the ‘Value’ of these to false indicating that :-
- The ‘error’ or ‘task failure’ has been ‘handled’
- The error should not be further propagated up the event handling chain.
- The package level OnTaskFailed event should not be triggered which would result in our failure record being written
- The Package level OnError event should not be triggered which would result in our Package returning a DTSER_FAILURE
After making all of these correction I am pleased to report that the error handling now behaves exactly as I expect it to and a failure during the SMTP send now generates only a success audit record upon completion of the package without the confusing extra failure audit row.