How can I capture SSIS events in my package at one place

Question

How can I capture SSIS events in my package at one place

Anjan Wahwar

SSCommitted

Points: 1617
More actions
February 6, 2014 at 8:59 am

#289214

Hi All,
I have a package built in SQL Server 2008. In that package I am capturing the following events (and log the message to my application db table like-Which task logs the message and the actual message) for each task:
1. OnError
2. PreExecute
3. PostExecute
I am calling a logmessage.dtsx package that takes 'task name' and 'message' as input and logs that into my table.
I am calling this logmessage.dtsx package n-time for all my tasks inside that package which is terrible experience for me. :w00t:
Now my question is-Is there a ways to capture all these events at one place and execute that package or dll (i can create that) that will log to my table instead of capturing events for all the tasks one by one?
Please let me know if I'm not clear enough in putting up my requirement.
Thanks,
Anjan
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 1

Not sure I follow you completely, but I use the Event Handler in pkgs to write logging messages to a table I created for such a purpose (we need to know when a data flow has finished, for example, or when a major delete is occurring and I can easily grab the last 100 records written to Logging to see what step every executing pkg is in.

It's a simple matter to copy/paste these event handlers across pkgs (a few mods needed, not big and time consuming). We can then write queries or extract any logging info needed.

Is this close to what you are seeking??

mz1derful Ten Centuries Points: 1234 More actions · Answer 2

In my packages I have one stored procedure to handle all events I want to log. In the Event Handlers tab, I create an Execute SQL task to call the stored procedure, pass in the variables and write the data to an error table. One stored procedure is called, so you just have to copy that error handler task to the tasks you want to log an error. No need to create a new package for error handling.

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 3

Even I'm doing the same thing. The only difference is I'm using sepate pkg to write my logging logic & calling it inside event handlers.

My actual question was, is there a way to call the longing task once in the whole pkg for logging at task level?

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

mz1derful Ten Centuries Points: 1234 More actions · Answer 4

I understand -- If you add the event handler to just the package failure tab and set all your tasks to FailParentOnFailure = false and FailPackageonFailure = true then when any task fails it will capture the package failure event so you just have to add the error handler to the package level error event tab. I hope this makes sense.

Lempster SSCoach Points: 15651 More actions · Answer 5

herladygeekedness (2/17/2014)
It's a simple matter to copy/paste these event handlers across pkgs (a few mods needed, not big and time consuming). We can then write queries or extract any logging info needed.

Even simpler than copy/past, if you create a template package and build your event handlers in that package, you can base all your packages on your template and hey presto, all your event handlers are already in place! The link below provides details onhow to make a template package available.

http://technet.microsoft.com/en-us/library/ms345191(v=sql.105).aspx

Regards

Lempster

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 6

Well!! I guess template will not work for me. Let me try to rephrase my requirement.

My ssis pkg contains many task (on the avg 20 task) and for each task i need to write event handler for capturing three event - OnPreExecute, OnPostExecute and OnError. Now I have already copied and calling my logging pkg from each task' event handler and currently its working good.

Now what I wanted to change is to make this logging task running at one place, may be at package level, and log the events for al the task. So, in future if i need to change my logging logic, i just need to update it at one place not for all the 20 - 30 task in my package.

Is it possible with SSIS?

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 7

Karen A. Ferrara-426245 (2/18/2014)
I understand -- If you add the event handler to just the package failure tab and set all your tasks to FailParentOnFailure = false and FailPackageonFailure = true then when any task fails it will capture the package failure event so you just have to add the error handler to the package level error event tab. I hope this makes sense.

Thanks Karen,

I will try this one.

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!