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).

»


See All Articles by Columnist
Marcin Policht