Understanding Event Handlers


Now that you understand some of the basic methods provided by SSIS for detecting and handling errors, it's time to learn how you can use event handlers to perform some of these tasks. Event handlers are workflows that enable SSIS packages to respond programmatically to events that are raised at run time by a package, container, or task. Events signal a number of different states, including error conditions when a task starts, when a task completes, or when a change occurs in variable status. For example, an OnError event is raised when an error occurs.

Using Event Handlers to Perform Tasks

Event handlers can perform tasks such as the following:

  • Clean up temporary data storage when a package or task finishes running

  • Retrieve system information to assess resource availability before a package runs

  • Refresh data in a table when lookup in a reference table fails

  • Send an e-mail message when an error or warning occurs or when a task fails

Event handlers eliminate redundancy by providing the same functionality for an event regardless of which task or container generated the event. For example, if you want to send an e-mail message when any task fails, you can put a Send Mail task in an OnError event handler. This is significantly less work than having a failure precedence constraint and a Send Mail task associated with every task or container that might generate an error.

Triggering an Event Handler

Because a task is part of a container and a container is part of a package, when a task generates an error event, its containers and package also generate an error event. Likewise, when an event occurs, it can cause additional tasks to execute. In fact, event handlers can cause other event handlers to execute.

When an event occurs and it does not have an event handler, the event is raised to the next container up the container hierarchy in a package. If this container has an event handler, the event handler runs in response to the event. If not, the event is raised to the next container up the container hierarchy and so on up to the package.

If an OnError event handler is configured for multiple tasks and containers, unexpected behavior can arise, and events appear to bubble up. To prevent an event from bubbling up, set the event handler's Propagate property to False.

Note 

Individual data flow transformations cannot trigger an event handler. If a transformation fails, the entire data flow task fails and generates an event.

Using the Event Handlers Provided by SSIS

For detecting and handling errors, the most important event handlers are OnPreExecute, OnPostExecute, OnWarning, OnError, and OnTaskFailed. Table 9-1 describes some of the event handlers that SSIS provides and describes the run-time events that cause event handlers to run.

Table 9-1: Predefined Events
Open table as spreadsheet

Event

Description

OnPreExecute

Writes a log entry immediately before the executable runs

OnPostExecute

Writes a log entry immediately after the executable has finished running

OnWarning

Writes a log entry when a warning occurs

OnError

Writes a log entry when an error occurs

OnTaskFailed

Writes a log entry when a task fails




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net