Stopping an SSIS Package on Data Flow Fail

Question

Stopping an SSIS Package on Data Flow Fail

nick.latocha

SSCrazy

Points: 2111
More actions
April 30, 2014 at 2:23 am

#292847

I have an SSIS package with 5 separate data flow tasks. They are not linked and run in parallel. I;m trying to figure out how to stop the other data flow tasks when one task fails. Anyone got any idea?
I tried setting Max Fails etc but doesn't seem to stop the other tasks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

nick.latocha (4/30/2014)
I have an SSIS package with 5 separate data flow tasks. They are not linked and run in parallel. I;m trying to figure out how to stop the other data flow tasks when one task fails. Anyone got any idea?
I tried setting Max Fails etc but doesn't seem to stop the other tasks.

Try to add a follow-up task (could be a script task or a ole command task that issues a SQL RAISERROR stmt). Connect your 5 parallel tasks to this new task but only upon failure.

nick.latocha SSCrazy Points: 2111 More actions · Answer 2

Hmmm that doesn't seem to work. The other Data Flow tasks seem to continue to run in parallel.

I tried something similar with a Script task which returned a dts error and that didn't work either.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

If they run in paralllel, they are independent of each other.

However, this is not 100% true. Say you have 7 tasks which can run in parallel.

But only 4 of time can run at the same time (due to the number of processors available). If one crashes, the other 3 will continue to run. But no new data flow tasks will be started since the parent container (package, sequence container, for each loop, whatever) has already failed.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

nick.latocha SSCrazy Points: 2111 More actions · Answer 4

Koen Verbeeck (4/30/2014)
If they run in paralllel, they are independent of each other.
However, this is not 100% true. Say you have 7 tasks which can run in parallel.
But only 4 of time can run at the same time (due to the number of processors available). If one crashes, the other 3 will continue to run. But no new data flow tasks will be started since the parent container (package, sequence container, for each loop, whatever) has already failed.

This makes sense

Thanks