Fail Package If any of the Task fails
Hi, i have an SSIS package having two Sequence Containers connected to Execute sql task in Parallel. I have an expression in between execute sql and both sequence containers if @Test = 1. @Test gets value from execute sql task. I want to fail the package if any of the two sequence container fails. So i set FailpackageOnFailure to TRUE for both containers but even though one of them fails, the package wont fail and other one still runs as i have an expression for both. Any ideal how do i solve this? This is how the package looks like:
July 20th, 2012 8:08pm

Sorry I missed something, there are two different variables @Test and @Test2 having expression between execute sql task and Sequence Containers.
Free Windows Admin Tool Kit Click here and download it now
July 20th, 2012 8:51pm

You are correct. You have expression in between sequence containers and execute sql task thats why even one of the container fails other one will run. There is one way you can achieve this, connect containers in sequence rather than in parallel and take the expressions out. Add two execute sql tasks in sequence containers, connect them to data flow tasks and have input variable @Task and @Task2 in both execute sql tasks. Now write some script there and declare @Test Set @Test = ? and say If @Test = 1 your script. same for @Test2. so if the value of @Test or @Test2 is not 1 it wont load the table but having FailPackageOnFailure = TRUE for both containers will fail the package if either one fails. This SHOULD work. Let us know
July 20th, 2012 10:04pm

Hi Nick Noranza, Answer: Put all (whatever you have shown here) into a sequence container. Thats it. Lecture: Whenever you come across this type situation where you want to fail the package if any of the task (what you can call - Unit of work) fails, then gather them and put them into sequence container. A sequence container - whatever is in side will be "single unit work". Thanks, Khilit http://www.bigator.com
Free Windows Admin Tool Kit Click here and download it now
July 21st, 2012 12:42am

Thanks Khilitchandra for your help but even though i put everything in 1 seq container and set failpackageonfailure true it was still running for if one of the container fails.
July 21st, 2012 11:00pm

Thanks SqlRockss, it worked.
Free Windows Admin Tool Kit Click here and download it now
July 21st, 2012 11:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics