Ideal Event handling Technique

SQL Server Integration Services

MShetty


Hi All,

We have this SSIS package that serves a ETL role, but sometimes when the package crashes we wanted our support personnel to be aware of it, so we did implement an Event handler Package, executable is the package and Event Handler on taskFailed. The idea is if any of the Tasks fail, then trigger the event handler.

Now recently I found out while debugging the package, one of the DFT's failed, and the control went to the Event handler. But thenrest of the Task's are executing and the package finished successfully.

How do I ensure that if at all some Tasks fail, the control should invoke the task at the event handler and end the package run.

One more query, what is the ideal and best way of Event handling to be followed in case of ETL jobs

Thanks in Advance.

Top

Re: Ideal Event handling Technique

joeydj


hi rick,

you can set precedence constraint (in the control flow on the line joining dataflows)

to "success" . however since you have an event handler you have to control the precedent inside the data flow by using a variable

regards,

joey






Top

Re: Ideal Event handling Technique

mverma

Group you DFT into a task flow container and for that container set the event handler.




Top

Re: Ideal Event handling Technique

Rick

I did move all of my tasks into a Sequence Container and set the event handler for the container. But no vain. Its still the same old story.


Top

Re: Ideal Event handling Technique

mverma

There is a properties of for a container named as "Fail Package on failure" if it is false then change it to "TRUE". and check package again.

after this if any task in that container fail then contain flow will not move to next task.





Top

Re: Ideal Event handling Technique

Rick

mverma wrote:

There is a properties of for a container named as "Fail Package on failure" if it is false then change it to "TRUE". and check package again.

after this if any task in that container fail then contain flow will not move to next task.

That's true, but I guess that property is there at the package level too, but the catch is that if we set the property, will the control move to the event handler to send out the mails, or end the package execution completely




Top

Re: Ideal Event handling Technique

mverma

DId your container, all data flow task are connected to the next DFT can you send me a screen shot of your pakage

Use this it may help you:http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp





Top

Re: Ideal Event handling Technique

Jamie Thomson

Rick wrote:
I did move all of my tasks into a Sequence Container and set the event handler for the container. But no vain. Its still the same old story.

Did you see Joey's post Simply make sure that your precedence constraints are set correctly.

Regards

Jamie





Top

Re: Ideal Event handling Technique

Rick

Jamie Thomson wrote:

Rick wrote:
I did move all of my tasks into a Sequence Container and set the event handler for the container. But no vain. Its still the same old story.

Did you see Joey's post Simply make sure that your precedence constraints are set correctly.

Regards

Jamie

Hi Jamie, If you checking this thread by any chance, I just have a slight doubt here, when all the DFT's are in a serial manner, yeah I agree that the precedence constraints will play a part, i.e if DFT 2 fails the rest of the DFT's will defintely not startup. In my case there are lot of independent tasks, ( made so to acheive more parallelism ) , I guess we have to accomdate each of them in Containers.

I am extremely sorry if what am saying is wrong or doesn't make sense. Just a thought Smile




Top