CODEWITHSUNDEEP
ssisevent-handlingpackage
Gidil
Gidil

Reputation: 4137

SSIS event handlers only for package level

I have several packages that need to be run in a specific order. To do this I have an additional package that runs all the rest.
I added event handlers to each call to a package in order to show the user the status of the process (executing, success, fail) using OnError, OnPreExecute and OnPostExecute.

What I didn't realize was that on every success of each part of the package it returns an OnPostExecute and then a new OnPreExecute when starting the next part of the same package.
This causes confusion for the user since he thinks a stage has been completed but then sees that it's still executing.

How can I have the event handlers only refer to package level events?
Any other suggestions would be appreciated as well.

Thanks!

Upvotes: 1

Views: 3570

Answers (2)

Mitul Panchal
Mitul Panchal

Reputation: 632

A simple solution is to create an SSIS event log table into a database and specified user variable on each event handler parameter and which are available in the list. Then take an Execute SQL task for that create a connection and add the following query into

INSERT INTO dbo.SSIS_events (EIGUID, PackageName, SourceName, EventInfo) VALUES (?,?,?,'OnPreExecute')

To track specific task.

Upvotes: 1

White_Raven
White_Raven

Reputation: 166

The solution I came up with would be to use one of the system variables that are avaliable in event handlers. In each of OnPreExecute, OnPostExecute and OnError scopes You can find at least a @[System::SourceName] variable.

@[System::SourceName] contains the name of the task raising the event.

Using it you can check if event was raised by the top-level package itself, one of the Execute Package Tasks, or any other task in package.
This way, even though we can't stop the multiple calls, we can control the response.

For example, most likely you would use a precedence constraint based on @[System::SourceName] that checks if it is the overall package name.

It's not the most elegant solution (it will require You to either have consistent names of EP Tasks, or to hardcode them in event handlers), but it works.

Sadly I couldn't find a way to prevent events from subpackages to be raised in main one.

Upvotes: 3

Related Questions