SSIS for each loop propagate not working

Question

I have a loop which loops through connections. Each loop connects to a different connection and then loads tables and such. In the 2nd step is where it connects and then loads data into our DWH. Sometimes the connection is down for what ever reason and will fail at this step. I need the package to keep going on this connection fail.

I have read many things on the propogate set to false and this still does not work. As you can see in my screen shot, i have the "Load ..." onError event handler propogate set to false, and the Sequence container onError propogate set to false.

I have also tried setting the sequence container max errors to 0 so that section completes, and on the onError of the "Load ..." to set a flag in a variable to continue going if the connection completes, or stop there if the connection fails.

I have done this in the past and just set the overall package complete status to success on completion, but this will not catch other errors that may occur in this loop that I will need to catch / fail the package on.

Any help here would be appreciated.

Answer 1

Doing more research on connections failing, I found a script by Jamie Thomson: Verify a connection before using it [SSIS].

I modified it a bit to my own usage:

1. Only used a single connection instead of looping through all of them.
2. I set the task result to always succeed.
3. Instead of FireError I did a FireWarning.
4. I created a variable (connFail) to set to a 0 or a 1 depending on if the connection failed or not.

I places this script task before my table load to catch any failed connections before the task was executed. These modifications allowed me to fire an e-mail alert if the connection failed (connfail = 1), or continue on the package if the connection was successful (`connFail = 0').

Full script I used is below:

bool failure = false;
bool fireAgain = true;

try
{
    Dts.Connections["Connection"].AcquireConnection(null);
    Dts.Events.FireInformation(1, ""
        , String.Format("Connection aquired successfully on '{0}'", Dts.Connections["Connection"].Name)
        , "", 0, ref fireAgain);
}
catch (Exception e)
{
    Dts.Events.FireWarning(-1, ""
        , String.Format("Failed to aquire connection to '{0}'. Error Message='{1}'",Dts.Connections["Connection"].Name, e.Message)
        , "", 0);
    failure = true;
}

if (failure)
{
    Dts.TaskResult = (int)ScriptResults.Success;
    Dts.Variables["connFail"].Value = 1;
}
else
{
    Dts.TaskResult = (int)ScriptResults.Success;
    Dts.Variables["connFail"].Value = 0;
}