Tuesday, 1 July 2014

Prevent events executing multiple times

Case
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




















Thursday, 26 June 2014

Nested includes in BIML Script

Case
I want to use nested includes in a BIML Script (an include in an include), but the second level isn't working. It seems to skip it without giving an error.
No second Sequence Container













Solution
First be careful with (too many) nested includes! It could make your BIML script obscure. There are two tricks to solve this problem. They came to me via twitter from @cathrinew and @AndreKamman.

Solution A:
Use a full path in the include tag instead of only the name:
Using the fullpath













Big downside is of course the full path in your BIML Script. In a multi-user environment with for example TFS that could be an issue because everybody needs the same project path.

Solution B:
A better option is to use CallBimlScript instead of include:
Using CallBimlScript












And you could also pass parameters to the included file and use relative path and then reuse the file in multiple projects.
Related Posts Plugin for WordPress, Blogger...