Foreach Loop Issue

Here is what I am attempting to get accomplished. I have an SSIS package that contains a Foreach loop container. This container executes a number of SQL tasks in order: SQL Task 1, SQL Task 2, SQL Task 3.

if the SQL task 1 succeeds it should flow on to SQL Task 2 and 3. This works fine when the SQL tasks do not fail...

In the event of any SQL Task failing control should flow to a send mail task to alert about the failure. Next the Foreach loop container should go to the next enumeration in the Foreach loop container and start the next new SQL task 1. So far I have been able to get the control to flow to the send mail task when a SQL Task fails. What does not work is when one SQL Task fails the entire Foreach loop fails and does not move to the next enumeration. It should only fail the package and move on.

Any help would be appreciated....

Please check the FailPackageOnFailure and FailParentOnFailure properties of ForeachLoop Container as well as Execute SQL Task Object. if any of them is defined as true then set it to false.

If this will not help you let me know.

|||

I have checked these properties and i have both of them set to 'False'... Still Fails...

Any other suggestons?

|||I suppose you could ForceExectionResult = Success|||

Hi Steve,

Here is the Solution:

1. For Foreach Loop container set "ForceExecutionResult" to "Success" so that this container never failes on execution.
2. For precedence constraint of all your tasks in Foreach loop container set the "Value" property as "Completion" so that next SQL task get executed only on COMPLETION of previous SQL task and not SUCCESS of previous one.
3. Set the "FailParentOnFailure", "FailPackageOnFailure" property to "False" for all SQL Task in container. Set "ForceExecutionResult" property to "None" for all SQL Task in container.
4. I am sure you are using "Failure" precedence constraint to send mail task from SQL Tasks.

I created a test package to try this scenario and it works :)

Thanks
Mohit