EventHandlers firing events

I ran into a pretty bizzare behavior in SSIS:

I am trying to set up a package with a built-in auditing. It has a OnPreExecute, OnPostExecute and OnError event handlers. I am trying to record when the package starts, completes, and the completion status. Each one of these event handlers has a script task that does the logging. I put in debug message boxes into each event handler script to understand what goes on. So here's the sequence of events:

1. When starting the package the OnPreExecute event fires. Right away it fires the second time. I'm guessing what happens here is the script task within the event handler fires its own OnPreExecute event - that's how the first message really pops up. The second message is generated by the actual package-level OnPreExecute event.

2. I have a condition within the OnPreExecute event handler which might set the task status to failure. You would expect the OnError handler to fire, right?.. Wrong! The package dies without firing either OnError or OnPostExecute event....

3. If i remove the condition in step 2, and force an error in the package body, i get an OnError event, and then 2 OnPostExecute events ( i guess for the same reason as in step 1).

What I'm trying to understand is why in the world my OnPreExecute and OnPostExecute events get fired by their own event handlers, yet when i fire other events within these event handlers the appropriate (other) event handler does not run.

Any ideas will be greatly appreciated.

Chianuri,

Is your package empty, or does it contain a single task? The package container itself will fire an OnPreExecute event as well. Your event handlers won't trigger themselves which is why throwing an error in the event handler doesn't cause the OnError event to fire. More information on Event Handlers can be found here: http://msdn2.microsoft.com/en-us/library/ms140223.aspx.

For the events that you've noted, as well as a number of others, you can audit them directly using the built-in logging functionality (http://msdn2.microsoft.com/en-us/library/ms141212.aspx).

~Patrik

|||

Patrick,

Thanks for the reply. What I described in my post is really a significant oversimplification of the system I am trying to build. The system requires a fairly sofisticated auditing mechanism and the built-in logging is not sufficient (unfortunately).

I would really like to utilize event handlers for all of my auditing needs. For that to happen, I have to be able to detect events happening within event handlers (such as exceptions occuring, etc.). Do you have any suggestions as to how to force one event handler to respond to events happening in another event handler?

Thank you.

-alex

|||

Alex,

You can add event handlers to the tasks contained within event handlers. The limitation you might find is that you cannot select an existing handler to handle the new events. What would you like to have happen when an exception occurs within your event handler?

Note within your event handlers you can set the variable CancelEvent to a non-zero value to tell the task which fired the event to stop running (http://msdn2.microsoft.com/en-us/library/ms140223.aspx).

I'd also like to encourage you to make suggestions on http://connect.microsoft.com if there are scenarios that we currently don't cover.

Thanks,
Patrik

|||

Patrick,

you are absolutely right - event handlers entering an infinite chain of events firing within event handlers is a concern. I think though, that a mechanism preventing that from happening can be designed fairly easily ( a quick and dirty one that comes to mind right away would be limiting the number of times the event handler can execute for a particular container, or explicitly preventing it from handling events fired by an executable it contains).

Thanks for your help. I'll think of a more robust solution and propose it on the connect site, as you suggested

-alex

|||

chianuri wrote:

I would really like to utilize event handlers for all of my auditing needs. For that to happen, I have to be able to detect events happening within event handlers (such as exceptions occuring, etc.). Do you have any suggestions as to how to force one event handler to respond to events happening in another event handler?

Alex,

If you execute PackageB from an "Execute Package Task" in PackageA then all actions in eventhandlers in PackageB will be caught by eventhandlers in PackageA.

Hope that helps.

-Jamie

|||

Thanks, Jamie. Unfortunately, this does not help me. One of the things that I'm trying to achieve here is knowing the sequence of package calls (e.g., I need to know that PackageA called PackageB which called PackageC) and I'm trying to capture the fact that each package starts and completes, as well as the execution status through event handlers... I'll have to think of an alternative.

Btw, I'm a big fan of your blog. Thanks for making it easier for the rest of us.

-alex

|||

chianuri wrote:

Thanks, Jamie. Unfortunately, this does not help me. One of the things that I'm trying to achieve here is knowing the sequence of package calls (e.g., I need to know that PackageA called PackageB which called PackageC) and I'm trying to capture the fact that each package starts and completes, as well as the execution status through event handlers... I'll have to think of an alternative.

Btw, I'm a big fan of your blog. Thanks for making it easier for the rest of us.

-alex

Well perhaps you could employ some sort of naming convention. e.g. Every package is called Pkg*.dtsx

That way you can check the System::SourceName property to see if the event is raised by a package. That way you will know when a package starts/stops.

-Jamie