Today, I finally came around to fixing a bug that had been in our SSIS packages for a while. We've implemented a logging infrastructure based on this article from Jamie Thompson. In short, we use event-handlers to log audit info and errors to our database. We also use a custom task to read some configuration settings from file – stuff like database connection strings and so (The reason we use a custom
task is that I didn't really like the configuration features built into SSIS, but that's a topic for another time). To make sure that the custom task was executed before any other task, I just added it to the pre-execute event of the package itself. Knowing that SSIS events 'bubble up' through the event-handler hierarchy, I put all of the other tasks in a Sequence Container and blocked the event-bubbling on that level (set System::Propagate to false on the sequence container's pre-execute event handler).
Based on the information I had, this should do the trick. Unfortunately, when I ran my package, I noticed that my custom task still was called multiple times.

Today, I finally took some time to find out what was happening. I always had assumed that the System::Propagate variable wasn't set correctly or something like that. So I set-up a small package to test the event-propagation: the package consists of the following control-flow:

Control Flow

Two event handlers are defined: one on the package and one on the sequence container. Both handlers contain a script that just dump the sourcename (ie. the task that caused the event) to the output. The execution order I expected is:

1. Package starts
    EventHandlerTest.onPreExecute event-handler

2. Sequence container starts

MySequence.onPrexecute event-handler

EventHandlerTest.onPreExecute event-handler

3. DummyScript starts

MySequence.onPrexecute event-handler (and stops event from propating to package event handler)

However, when I run this package (with System::Propagate=false on the sequence container), I was surprised by the output:

1. EventHandlerTest.onPreExecute: EventHandlerTest
2. EventHandlerTest.onPreExecute: MySequence PreExecute
3. MySequence.onPreExecute: SEQC MySequence
4. EventHandlerTest.onPreExecute: MySequence PreExecute
5. MySequence.onPreExecute: Dummy Script

The first line is ok: the package event handlers catches the pre-execute event of the package itself. The second line however is one I didn't expect: it shows the package event handler handling the event thrown by the sequence container's event handler! So the tasks defined in an event-handler also throw events! Although this makes perfect sense from a technological point of view (after all, an event-handler is just another taskcontainer), I still find it strange that the event-handler causes
an event by itself! Unfortunately, I have found no way to disable these events.

So, it turned out that the System::Propagate was correctly defined. The event-handlers I had defined on the container level were causing the custom configuration task to be loaded multiple times. The workaround was then to move the custom task to the main body of the control flow, but outside of the sequence container (to be sure that the configuration is loaded before any of the sequence container event-handlers are fired). Simple enough, just took some time to figure this one out!

Advertisements