December 29, 2009 at 2:53 pm
I have a test package I'm working on with an execute sql task. on posteexecute I need to access the execute sql task's ForcedExecutionValue property from a script task. How do I do this?
Here's what I'm looking for:
1. Determine who/what called my EventHandler
2. After determining what called my EventHandler, expose the properties of that object to me for reading
I'm going blind with my google searches..last straw here.
December 29, 2009 at 11:47 pm
[font="Comic Sans MS"]
Don't mean to offend you - but would it be possible to tell what exactly you wish to achieve here? Things that you wish to achieve might be done using simple ssis components and flows..
Also - please can you elaborate this -with an example perhaps?
expose the properties of that object to me for reading
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 30, 2009 at 6:50 am
Sure I can elaborate but don't want to lose sight of the original question. What I want to do is create an event handler that takes a table name as a parameter and fires an executesql task. I want to create this event handler at the package level and make it generic so that it doesn't care what table gets passed to it, just needs the table name and the procedure it calls will do the work. Right now I can store the table name in the description field of the executesql task in the control flow portion because the System::SourceDescription variable is exposed and I can pick this up. However I want to store the table name elsewhere. What I'm trying to do is get some standardized logging across all of our packages which right now have very inconsistent logging. I can't make this overly complex or rewrite too much because the developers will never implement it. It needs to be as simple as possible and this approach would only require them to update x field in their execute sql tasks and drop an executesql componenet in their onpre and post execute events for logging. At the end of the day i want them to have pre/post row counts and tablenames logged to a table for each data load that they do. Can't use the row count transform because that is in the data flow task, not control flow. I still want an answer to my quesiton because now regardless of my approach I want to know the answer for future reference. An event handler should be able to know what task called it and have read only access to the calling object's properties.
December 30, 2009 at 7:34 am
[font="Comic Sans MS"]
Does this mean a developer need to put a total of 10 'generic event handler' on pre and post execute steps in case a package loads 5 table?
The variable System::SourceName instead of description field and you can use a standard to put name of the task to be coupled to the table being loaded.
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 30, 2009 at 7:41 am
No, because at the package level the event handler would be fired, not an event handler for each execute sql task. You're right, I could use the source::name but that's no better than source::description, which would probably be better because people use the description field far less than the name field. But to my orinal question, there has to be a way to access properties beyond the Source:: variables provided to read them. I guess the question I should be looking at then is how do the Source:: variables get populated which will in turn lead me to my answer. If anyone knows this that would save me some looking.
December 30, 2009 at 8:21 am
[font="Comic Sans MS"]
If this is on package level - it will be executed twice before and after every single executable. So you would definitely need some logic on the stored procedure to act differently depending on defferent type of component (you might not be interestted to log script task - file system task etc).
The only way to do this would be reading system::sourcename or system::sourcedescription...
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 30, 2009 at 8:28 am
I'm ok with it executing pre and post because i want to catch the pre/post rowcounts when my handler runs. I totally agree with you that I wouldn't want some things to fire the handler so I'd want the type of executable that called my handler, which leads me back to wanting to examine what called me. I can't believe that source::name and source::description are my only choices, there has to be a way to tell what called me outside of the source:: variables and that's what I'm trying to get to.
December 30, 2009 at 8:38 am
mikefemenella (12/30/2009)
I can't believe that source::name and source::description are my only choices, there has to be a way to tell what called me outside of the source:: variables and that's what I'm trying to get to.
[font="Comic Sans MS"]I can understand - but I am not aware of a method with which you can do this. This is an interesting question - anyone else?[/font]
[font="Comic Sans MS"]--
Sabya[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply