Event handlers in SSIS Dataflow task

Question

Event handlers in SSIS Dataflow task

a2zwd

SSCrazy

Points: 2356
More actions
October 18, 2011 at 4:19 am

#249663

Hi
I have a package with some exec sql tasks and 1 data flow task.
In the dataflow task, data retrieving from XML source to multiple OLEDB destinations.
All OLEDB dests are running well where as one in just Yellow color(not green nor red). Actually I had placed OnError event handler to DataFlow task.
This is yellow because it has generated error (violating not null constraint on one column).
My question is Why is it yellow? It would have run OnError event handler(Exec Sql task) and proceed to next tasks from Data Flow?
How can I catch errors if any of the transformation in Data Flow generates error and not failing the package and continue to execute downward tasks.
Thanks in advance

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What does the event handler do? Is it possible that the task is still yellow because the event handler is stuck doing something? You may wish to experiment using the OnTaskFailed event handler instead - not sure whether that executes asynchronously, allowing the package to move on to the next task.

John

a2zwd SSCrazy Points: 2356 More actions · Answer 2

Hi

Thanks for the reply. OnError event handler will log the error details to a table.

I forgot to tell you, I had OnTaskFailed event handler also enabled on DataFlow task.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

OK, and during execution, what colour are the two data handlers when your data flow gets stuck on yellow?

John

a2zwd SSCrazy Points: 2356 More actions · Answer 4

Hi

Thanks for the reply.

The EventHandler(OnError) will truncate the same table which is trying to insert into that has failed. I think dead lock occured. How do I release the lock or how can I truncate the tables. It is actually staging process. So I am cleansing the staging tables if an error occured.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Sounds like you need an event handler on your event handler! Seriously, though - I would start by having it not do anything except write the error to a separate table. When you've got that working properly, then you can change it so that the event handler also takes remedial action if that's what you require.

Deadlocks are automatically resolved by SQL Server - one of the the processes involved gets terminated.

John

a2zwd SSCrazy Points: 2356 More actions · Answer 6

Hi,

I have removed the cleansing task and just captured the error description(Log the error).

But, after execting onerror event handler(showing green), dataflow task fails(Red) not proceeding to next tasks. Why?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

You would need to configure your package so that it does a specific thing on task failure. I think by default, the package will fail if only one task fails - but you can increase that threshold. Also, you need to watch out for failed tasks causing parent containers to fail. You can suppress that behaviour, too.

John