Pre and Post Exec Event Handler For a Package

I have a simple test package to play with Event Handler. I got 2 Exec SQL Task and one File System Task. I have set the OnPreExecute and OnPostExecute at package level along with OnPreExecute on SQL and File System Task. These event handlers were loading the package, task name along with start time, machine and user in my audit table (using Exec SQL Task).

OnPreExecute did work fine for SQL and File System Task. They only had one record in the Audit table.

OnPreExecute and OnPostExecute for the package itseld did NOT work. When I say it doesn't work, it logs it to my audit table so many times. I was expecting one record in my audit table for OnPreExecute and one record for OnPostExecute at Package Level. But I was worng. It has inserted into my audit table 14 times instead of twice.

Any ideas? Or am I doing something wrong?

I know I can fix this by workaround. I can remove OnPreExecute and OnPostExecute Event Handler at Package level and insert Exec SQL Task at the beginning and end of the package. This will fix it, but I was wondering why it doesnt work?

Thanks
SuthaI don't quite know why you're getting it 14 times but this will be something to do with the same event getting caught in multiple places.
Every time the File System Task (for example) executes it will fire the eventhandler scoped to the task AND the eventhandler scoped to the package. So your event actually gets handled twice.

-Jamie|||Yeah i concur from our observations that this seems to be what is happening but surely this is a bit of a design issue ....

I want to know from the post execute that the package is starting (or has started), i don't want the startup events from each component thrown up to the post execute event on the whole package because they don't have any relevance at this level.

What do you think ?

Mark Hill|||Jamie

I tried it on a brand new package as well. I can re-produce it. As Mark states that it looks like an design issue. Anyone from MS can shed any light on this?

Thanks
Sutha|||

You can easily debug it like that:
- place a script in package's OnPreExecute event handler
- set a breakpoint on the task (F9)
- run in debugger
- when debugger brakes on the breakpoint add SourceName variable to the Watch window.

Now, every time you hit the breakpoint you will see the name of the task/container that raised the event.

I believe that all OnPreExecute events in your package get propagated to the package level. That's why you see so many of them. If it is not what you expect - you can change it setting Propagate system variable on event handlers to False