Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I capture SSIS events in my package at one place Expand / Collapse
Author
Message
Posted Thursday, February 06, 2014 8:59 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 63, Visits: 236
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.

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 !!!
Post #1538716
Posted Monday, February 17, 2014 8:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 11:21 AM
Points: 285, Visits: 768
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??

Post #1542165
Posted Tuesday, February 18, 2014 6:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 46, Visits: 180
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.
Post #1542525
Posted Tuesday, February 18, 2014 6:35 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 63, Visits: 236
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 !!!
Post #1542535
Posted Tuesday, February 18, 2014 7:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 46, Visits: 180
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.
Post #1542590
Posted Tuesday, February 18, 2014 8:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:12 AM
Points: 1,887, Visits: 1,179
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
Post #1542600
Posted Tuesday, February 18, 2014 10:48 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 63, Visits: 236
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 !!!
Post #1542672
Posted Tuesday, February 18, 2014 11:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 63, Visits: 236
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 !!!
Post #1542685
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse