Explore how SQL Server 2008 Integration Services’ events are triggered during package execution and the ability to react to their outcome through event handlers.
In its most basic form, creating
SQL Server 2008 Integration Services
Business Intelligence is a relatively straightforward process that leverages
Studio’s intuitive (based on
design guidelines) characteristics of its
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
package initiates a series of actions associated with its executable
components, namely tasks and enclosing the containers (such as
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
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,
relevant exclusively in the context of
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
precedence constraint or by forcing handler failure with
- 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
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
- 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
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