Expert SQL Server™ 2005 Integration Services

7.1. Error and Event Handling

Event handling is, at its root, the main differentiating factor between SSIS and writing a 10-page TSQL script to transform your data. With event handlers, you can watch certain areas of the package or the complete package and react to errors. This section also shows you how to use event handlers in a few more advanced ways.

7.1.1. Types of Event Handlers

When you create an event handler, you must specify what type of event you want to trap. The main types of events to watch are going to be PreExecute, PostExecute, OnError, and OnWarning. The PreExecute event is executed any time a package, container, or task starts. If you were to have a package that had a sequence container with two tasks, you would see the PreExecute event executed four times in the packages execution: once for the package beginning, another time for the container starting, and once for each task. The PostExecute works much the same way, but is executed for every time a package, container, or task completes.

The OnWarning and OnError events are executed any time a warning or error is executed. In the same theoretical package mentioned earlier with a package, container, and two tasks, an OnError event handler would only be executed once if only a single task inside the container were to fail. You can also trap any type of informational event with the OnInformation event handler. This is generally going to be overkill, though, since the event is executed dozens of times (typically) ...