I have a log task in an OnPreExecute and OnPostExecute event handler, but it executes multiple times. Why is that and how can I prevent it?
Solution
This is because all tasks and containers fire events and these events are propagated to their parent container and then to their parent container and so on. This means that if you have a package with a Sequence Container with and with an Execute SQL Task in it, that each of them fires events. Let's test that.
For testing purposes I added an Execute SQL Task in each event handler with an insert query to show which events are fired. Each task inserts the name of the event and the value of the System Variable SourceName in a log table with an identity column LogId.
 |
| Log all events, executable is package |
When you run the package you can see that for example the OnPreExecute event has three records:
1) the Package
2) the Sequence Container
3) the Execute SQL Task
 |
| Event handlers executing multiple times |
The trick
The trick to execute the Execute SQL Task in the event handler(s) only once, is to check whether the source of the event is the package and not one of it's children (containers/tasks).
Add a dummy Script Task or an empty Sequence Container in front of the Execute SQL Task and add a Precedence Constrain expression between them: @[System::SourceName] == @[System::PackageName]
 |
| Expression to filter events that are not from the package |