Tuesday, 1 July 2014

Prevent events executing multiple times

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?

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

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...