Hi all,
does anyone know how to do event handlers in SSIS?
at the moment i've attached a send email tak to each failure from data flow tasks, but very hard to manage it.
was wondering how i can store the error message and maybe post it at the end of the project execution?
any help?? please
I ran into a pretty bizzare behavior in SSIS:
I am trying to set up a package with a built-in auditing. It has a OnPreExecute, OnPostExecute and OnError event handlers. I am trying to record when the package starts, completes, and the completion status. Each one of these event handlers has a script task that does the logging. I put in debug message boxes into each event handler script to understand what goes on. So here's the sequence of events:
1. When starting the package the OnPreExecute event fires. Right away it fires the second time. I'm guessing what happens here is the script task within the event handler fires its own OnPreExecute event - that's how the first message really pops up. The second message is generated by the actual package-level OnPreExecute event.
2. I have a condition within the OnPreExecute event handler which might set the task status to failure. You would expect the OnError handler to fire, right?.. Wrong! The package dies without firing either OnError or OnPostExecute event....
3. If i remove the condition in step 2, and force an error in the package body, i get an OnError event, and then 2 OnPostExecute events ( i guess for the same reason as in step 1).
What I'm trying to understand is why in the world my OnPreExecute and OnPostExecute events get fired by their own event handlers, yet when i fire other events within these event handlers the appropriate (other) event handler does not run.
Any ideas will be greatly appreciated.
It is possible for a task to be executed multiple times in parallel. e.g. If a package is executed more than once using "Execute Package Task".
I understand that in the future the ForEach container will have the ability to execute all its iterations in parallel as well.
This is a problem. In the eventhandlers we know the SourceID of the container raising the event so we can tie together events raised by the same container but this simply isn't possible if the events are running in parallel.
An example of where we would want to do this is drop a record into a table when a container fires OnPreExecute and then update that record with the container duration time upon OnPostExecute. This does NOT work when tasks run in parallel.
The solution to this is very simple. As well as capturing System:ourceID, capture a new value, System::ExecutionID as well.
In the meantime, can anyone think of another way around this?
cheers
-Jamie
P.S.
I have raised this bug here: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK46904 and have asked for the provision of System::ExecutionID. If you think its a good idea then plese go ahead and vote for it.
[Microsoft follow-up]
This is still a valid request. Does it need opening on Connect?
-Jamie
Does anyone know how to create an eventhandler for a dataflow task specific events (OnPipelinePostEndOfRowset, OnPipelineRowsSent, etc.)? These events are available for logging via the standard logging infrastructure, but there seems to no eventhandler for them.
The reason I'm interested is that parsing information logged by these events using builtin log providers is not easy (eg., the number of rows sent gets burried somewhere in the message column (i'm using sql provider). I'd like to capture this information and record it cleanly in a custom ssis metadata database i'm building. Any ideas are welcome. Thanks.
-alex
[Yes, I know I keep posting this, and I'm sorry for that. But I want someone to confirm that this is a bug and not by design. I feel it is very very important]
All,
I'd like someone from MS to confirm something for me if possible.
I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.
Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.
-Jamie
If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip
It doesn't need any configuration because there are no connection managers. You can just run it standalone.
Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure".
Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.
Reply here or email direct with any queries!
All,
I'd like someone from MS to confirm something for me if possible.
I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.
Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.
-Jamie
If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip
It doesn't need any configuration because there are no connection managers. You can just run it standalone.
Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure".
Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.
Reply here or email direct with any queries!
[Microsoft follow-up] This needs either fixing or explaining.