SSIS Event Handlers

There was a question recently by a fellow MVP, who was wondering if the event handlers in SSIS were fired synchronously or asynchronously. The answer (which I double-checked with Matt Masson) is that they are synchronous. When events are fired asynchronously, they are queued up, and the system runs them as it gets to them. The main thread of the program may continue executing while the events are firing. However, when they are run synchronously, the main thread is paused while the event is executed. Events are not queued up, they are executed as soon as they fired.

What does this mean for your SSIS packages? Well, for the most part it reduces potential issues that can be caused by events firing out of sequence. There can also be threading issues with asynchronous events. So generally using synchronous events makes the programming model a little simpler. It also means that any work done in the event handler will be complete prior to the main thread picking back up. For an example, take the following package, which performs some setup prior to running a data flow. Assuming the setup runs correctly, the data flow should be run.

image

The Perform Setup Work has an OnPostExecute event handler that verifies that setup was performed properly, and sets the value of a variable to 1 or 0, depending on whether it was successful or not.

image

The expression on the precedence constraint checks the value of the variable, to determine whether the data flow should be executed.

image

Since the event handler is executed synchronously, you can count on any work that is done in the event handler being complete before execution moves on from that task. That includes the setting of any variables in the package, which makes the above approach possible. If the event handlers were run asynchronously, you would not be able to count on the variable being set before the expression on the precedence constraint was checked.

However, because your main package execution will be paused while event handlers are firing, you’ll want to make sure that any code in the event handlers runs quickly. You can’t use the event handlers to queue up long running processes, as you can in some languages.

Event handlers are a very powerful, but often underutilized, feature in SSIS. Hopefully this additional information will make them a little easier to use.

Leave a Reply