SSIS Events and Handlers

In its most basic form, creating SQL Server 2008 Integration Services packages using Business Intelligence Development Studio is a relatively straightforward process that leverages Studio’s intuitive (based on Visual Studio design guidelines) characteristics of its Designer interface. However, this simplicity is somewhat deceiving, hiding the complexity of underlying code and additional functionality associated with it. In this article, we will explore one of feature, which involves events triggered during package execution and the ability to react to their outcome through event handlers.

Launching an SSIS package initiates a series of actions associated with its executable components, namely tasks and enclosing the containers (such as Foreach Loop, For Loop, or Sequence). These actions, which represent the entire duration of the package runtime, can be grouped into two main categories – validation (responsible for evaluating whether successive tasks are expected to complete successfully given the current conditions) and execution (carrying out the actual implementation steps). Both of them are further subdivided into more granular units, depending on their relative chronological order (i.e. taking place before, during, and after validation and execution). While their primary purpose is to facilitate execution of an underlying code, they also raise events reflecting their status. Some of these events, in turn, are capable of triggering execution of custom actions defined through containers (resembling standard SSIS packages in their format) referred to as event handlers. This additional functionality can be used in a variety of ways, typically geared towards assisting with logging and troubleshooting or providing a basis for remediation of runtime errors and warning conditions.

SQL Server 2008 Integration Services offers a fairly large number of built-in event types. Some of them have a specialized purpose, pertaining to the characteristics of a task or a container they are part of. For example, OnPipelineRowsSent event is quite helpful for debugging purposes, but only when dealing with Data Flow Task tasks. Similarly, OnWMIEventWatcherEventOccurred and OnWMIEventWatcherEventTimeout are relevant exclusively in the context of WMIEventWatcher tasks. On the other hand, there are also several generic events, which are applicable to all executables (keep in mind that the term executable designates here any entity capable of running SSIS code, such as a task, a container, or the package itself). These types of events also have corresponding handlers configurable via the Business Intelligence Development Studio interface). Here is a brief listing summarizing their basic characteristics:

• OnError – triggered by a runtime error of a current executable. The corresponding event handler is commonly utilized in order to record data documenting circumstances of the failure, but it is not intended to terminate package execution (this can be accomplished, if desired, by leveraging OnFailure precedence constraint or by forcing handler failure with FailPackageOnFailure property set to TRUE).
• OnInformation – generated as the result of successful completion of a current stage of execution.
• OnWarning – complements OnError and OnInformation events, covering situations when an issue surfacing during component execution does not warrant raising an error condition.
• OnPreValidate – raised prior to the validation stage. This action might take place several times during package execution for a given component (depending on the value of DelayValidation property, which, when set to FALSE, eliminates initial validation).
• OnPostValidate – signals completion of validation stage of an executable.
• OnPreExecute – designates that an executable is about to be started.
• OnPostExecute – takes place when an executable finishes running. This (in combination with OnPreExecute) comes in handy when evaluating the performance of a component.
• OnExecStatusChanged – occurs when status (such as Abend, Completed, Executing, Suspending, or Validating) of a current executable changes.
• OnVariableValueChanged – allows you to detect changes to SSIS variables (providing that such variables have their ChangeEvent property set to TRUE).
• OnProgress – applicable if progress of a given executable is quantifiable (and can be especially useful for monitoring long-running packages). Additional information can be derived by evaluating system variables associated with the corresponding event handler (including ProgressComplete, ProgressCountLow, and ProgressCountHigh).
• OnQueryCancel – fires periodically, allowing you to cancel execution of a given component (assuming such task is feasible).
• OnTaskFailed – indicates failure of a task. It is typically accompanied by OnError event.

It is worthwhile noting that handlers are fired in a synchronous manner, which means that a thread triggering them waits for their completion before continuing its execution. In addition, they follow rules imposed by the hierarchical structure of SSIS packages. More specifically, by default, an event invoked by a given component will trigger a corresponding handler implemented not only on that component’s level, but also those defined for its parent and grandparents (for the matching event type). This allows you, on one hand, to ensure consistent behavior of all components in a package without code duplication (by creating a single event handler for the top container) and, on other, introduce exceptions by creating custom event handlers on an arbitrary level of package hierarchy. Further flexibility is made possible through manipulation of the Propagate system variable of Boolean type. Setting it to False within the scope of a container-level event handler will ensure that its parent’s handlers will not be invoked. In addition, by turning on the DisableEventHandlers property of a given executable, you have the ability to prevent its events from triggering any of its handlers (although this has no impact on event handlers defined in its parent and grandparents containers).

As mentioned earlier, event handlers are used primarily to track the progress of execution of individual components and troubleshoot any issues that might surface during package runtime. A collection of system variables helps you make information captured in this manner more meaningful. Some of them, such as SourceDescription, SourceID, SourceName, or EventHandlerStartTime are common to all event handlers, while others are available only while dealing with specific event types (such as ErrorCode or ErrorDescription, which apply specifically to OnError events or ProgressComplete, ProgressCountLow, ProgressCountHigh, and ProgressDescription pertinent to OnProgress events).

To conclude our coverage of event handlers in SQL Server 2008 Integration Services, let’s briefly examine the Business Intelligence Development Studio interface, which allows you to manage their configuration (it is also possible to accomplish the same outcome through programming methods, as described in the SQL Server 2008 R2 Books Online). Start by opening an SSIS package in the Designer window. Next, switch to its Event Handlers tab. Right underneath the top tabbed edge, you will notice two list boxes. The one of the left lists all of package SSIS containers and tasks displayed in the hierarchical format (with the package itself as the top-level node). The one to its right gives you the ability to choose an event handler to be defined for the component currently selected on the left (your options include all of the event types enumerated above). Once you make your choice, simply click on the empty area of the Designer interface and follow the same actions you used when designing SSIS packages to populate Control Flow tasks and Data Flow components (including adding Toolbox items via drag-and-drop, configuring system and user variables, and defining Connection Managers).