OnError Event Handler in SSIS Fires On Task Failure - A Bug?

Note: see also the updates to this post at the end.

I wasted about two hours of my day today trying to figure out why I was getting an extra fire of my package-level OnError event handler in SQL Server 2005 SSIS. At first I thought it was firing once for the original error occurrence (which I had inside of a Try block) and another time for the call to FireError(). This was not the case, however. Turns out the extra call happens when you set Dts.TaskResult to Failure.

That's right: once for FireError and once again upon failing the task.

Take a look at this sample code, from a Script Task:

'Assume success
Dts.TaskResult = Dts.Results.Success

Try
  'Imagine code here that calls something
  'that could raise an exception.

  System.Windows.Forms.MessageBox.Show("Before Throw")

  Throw New Exception("Forced error for testing.")

  System.Windows.Forms.MessageBox.Show("After Throw")

Catch ex As Exception
  System.Windows.Forms.MessageBox.Show("Before FireError")

  '-1 is a made up error number since the base System.Exception
  'class does not offer an error code or number property.
  Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)

  System.Windows.Forms.MessageBox.Show("After FireError")

  Dts.TaskResult = Dts.Results.Failure
End Try

Also picture a package-level OnError handler that shows a message box with the string "Package OnError." The Script Task, together with the firing of the OnError, produces the following sequence of message boxes:

Before Throw
Before FireError
Package OnError
After FireError
Package OnError

Notice the second "Package OnError"? That's happening because of the last line of the Script Task:

Dts.TaskResult = Dts.Results.Failure

I'm not certain whether this is a bug (though I suspect it is, and if it's not I'm not thrilled with the design), but I think I have found a way to work around it if the extra firing of OnError is problematic. Check out this post for information on how to retrieve the ErrorCode and ErrorDescription SSIS system variables. With that information you can use the ErrorCode and/or ErrorDescription values to tell when your getting an OnError event from the setting of TaskResult.

I have noticed, though I can't speak for why it's this way or whether it will stay this way, that when the OnError event is raised an extra time because of the failing of TaskResult, the ErrorCode value will be 4. I have also noticed that, with a Script Task at least, that the ErrorDescription will be "The Script returned a failure result."

This could be helpful in ignoring the extra firing on OnError to prevent, for instance, a duplicate log entry or notification. Giant caveat, however: I have not experimented enough yet to know whether the value of 4 is specific to just the Script Task or whether it would be the same no matter what kind of task. One reason I'm worried is that the ErrorDescription value has "The Script returned..." in it. Would that string be different for a different task type.

Update 1: Looking more closely at the debug output window, I can see now why setting the TaskResult to Failure is triggering an error. SSIS apparently sees the very act of doing so as an error in and of itself:

Error: 0x4 at TASKXXX: The Script returned a failure result.
Task failed: TASKXXX
Warning: 0x80019002 at PKGXXX: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

The "errors raised" is 2 because right before this point I had fired a FireError event in my Catch block.

I must say I really don't like this design. I still need to figure out if that error code of 4 is unique to the script task or if it will be the same for all tasks that fail--or is this behavior unique to the script task?.

Update 2: OK, I think I found a way for this not to bother me anymore: instead of setting Dts.TaskResult to Failed in my Catch block, I'm just going to use FireError and let MaximumErrorCount control whether or not the package should fail. I'm not sure yet whether I'm going to run into cases in which I'll want to fail a script task without producing an error, in which case the extra event would still be something to filter out in the OnError event.

If you find out any more details, please post them below.

Dan