SSIS Event Handlers

SSIS event handlers are the simplest means of turning an SSIS script into a reliable system that is auditable, reacts appropriately to error conditions, reports progress, and allows instrumentation and monitoring of your SSIS packages.

• They are easy to implement and provide a great deal of flexibility. Rob Sheldon once again provides an easy, clear introduction.
• At run time, executables (packages and Foreach Loop, For Loop, Sequence, and task host containers) raise events.
• For example, an OnError event is raised when an error occurs.
• You can create custom event handlers for these events to extend package functionality and make packages easier to manage at run time.

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 a lookup in a reference table fails.
• Send an e-mail message when an error or a warning occurs or when a task fails.

Event Handler Content

Creating an event handler is similar to building a package; an event handler has tasks and containers, sequenced into a control flow, and an event handler can also include data flows. The SSIS Designer includes the Event Handlers tab for creating custom event handlers.

You can also create event handlers programmatically.

Run-Time Events

The following table lists the event handlers that Integration Services provides and describes the run-time events that cause the event handler to run.

Add an Event Handler to a Package

At run time, containers and tasks raise events. You can create custom event handlers that respond to these events by running a workflow when the event is raised.

• For example, you can create an event handler that sends an e-mail message when a task fails.
• An event handler is similar to a package, it can also provide scope for variables, and includes a control flow and optional data flows.
• You can build event handlers for packages, the Foreach Loop container, the For Loop container, the Sequence container, and all tasks.

You create event handlers by using the design surface of the Event Handlers tab in SSIS Designer.

• When the Event Handlers tab is active, the Control Flow Items and Maintenance Plan Tasks nodes of the Toolbox in SSIS Designer contain the task and containers for building the control flow in the event handler.
• The Data Flow Sources, Transformations, and Data Flow Destinations nodes contain the data sources, transformations, and destinations for building the data flows in the event handler.
• The Event Handlers tab also includes the Connections Managers area where you can create and modify the connection managers that event handlers use to connect to servers and data sources.