This is the third part of a series on using checkpoints in SSIS. In the previous article, I went over how to use containers with checkpoints.  In this article, I’ll be extending the topic of containers to cover how event handlers can be utilized with checkpoints.

Note: this article applies to SQL Server 2005.

What are event handlers?

Event handlers are containers of workflow that are executed when a selected event is raised within a package.  Some of the more commonly used event handlers include:

• OnError
• OnPreExecute
• OnTaskFailed

Problems with using event handlers

When an event handler exists on a task that fails, the event handler might still execute depending on the type of handler it is.

For example, I have a Script Task in my package with the ForceExecutionResult property set to Failure to ensure its failure upon running the package.  This task has an OnPreExecute handler with a workflow of 2 Script Tasks.

When the Script Task starts, the OnPreExecute event handler will fire, causing both tasks in the handler to execute and succeed.  When they complete, the Script Task will then fail like we wanted it to.

Since the tasks within the handler succeeded, their IDs will be written to the checkpoint file, but the ID for the Script Task in the package control flow will not since it failed.  When the package is restarted, the Event Handler and the tasks in its workflow will not be executed again.

For this reason, many people assume that event handlers will not function properly when checkpoints are enabled, and consider this another reason why checkpoints shouldn’t be used in SQL 2005.

Solutions for using event handlers

OnPreExecute and OnTaskFailed Handlers

I don’t agree that checkpoints are useless if your package has event handlers, but I will say that getting the properties set correctly is not very intuitive.

To accomplish this, you will need to add a For Loop Container to the event handler workflow and put all of the tasks from the handler into the container.  Create a variable with a scope of the container.  I called my variable LoopCounter.  Use this variable to set the expressions for the loop.  This will cause the loop to only perform one iteration.

The FailPackageOnFailure property on the Script Task in the package control flow, as well as on the For Loop Container in the handler, need to be set to True.  The FailParentOnFailure property on Handler Script Task 1 and Handler Script Task 2 in the For Loop Container needs to be set to True.

Before executing the package again, make sure to delete the checkpoint file if it already exists.  Now, when you run the package, it the For Loop Container and all the tasks inside of it should succeed and turn green, and the Script Task should fail and turn red.

If you restart the package, the OnPreExecute handler will fire the exact same way as before.

You can use the same solution for an OnTaskFailed handler as well, although I did encounter some problems with the OnPreExecute handler not restarting when the OnTaskFailed handler also existed.

OnError Handler

For some reason, the OnError handler is slightly different than the OnPreExecute and OnTaskFailed handlers.

For this example, I will use the exact same workflow from the OnPreExecute handler that I created earlier by copying the For Loop Container (along with the tasks inside of it) to the OnError handler workflow.

Right now, the ForceExecutionResult property for the Script Task in the control flow is set to Failure.  This will cause the task to fail which will fire the OnTaskFailed handler.  Since we need it to fire the OnError handler, I have added the following line to the script so that it will throw an error.

    Throw New DivideByZeroException

At this point, if we were to delete the checkpoint file and run the package, it will work the same way as the OnPreExecute handler did in the earlier example, except this time a runtime error will occur on the script task and the OnError handler will be fired.

When the package is restarted, the entire OnError workflow will be ignored.  I haven’t quite figured out why this has a different outcome, which leads me to believe that it’s a bug in SSIS.

To get around this problem, you can set the ForceExecutionResult property of the last task in the For Loop Container, in this case, Handler Script Task 2, to Failure, or you can add a new script task as the last task in the container and set the ForceExecutionResult property for this new task instead.

Now, when the package restarts after failing because of the error, the OnError handler will always fire when the error is thrown.

Conclusion

This article is intended to show you how event handlers can be utilized with checkpoints in SSIS.  Here are some of the key points from this article.

• Placing the tasks from an event handler into a For Loop Container provides better control with checkpoints.
• Set the FailPackageOnFailureproperty to True on the For Loop Container.
• Set the FailParentOnFailure property to True on each task within the For Loop Container.
• In OnError event handlers, set the ForceExecutionResultproperty to Failure on the last task in the For Loop Container.
• An easy way to force a script task to fire the OnError handler is to add Throw New DivideByZeroException to the script.