I had a situation where I had to loop through a list of database connections and if one of the database connection was down the flow had to continue to the next database.

This is how I achieved it -

1. The list of database connections ( the result returned by the Execute SQL task : Retrieve hospital list ) was assigned to the user variable @HospitalList ( type Object)  and the list was assigned as  the enumerator  for the Foreach loop  show in the following diagram.

The Script editor had the following code.

public void Main()


{
// TODO: Add your code here

bool failure = false;

bool fireAgain = true;

foreach (var ConnMgr in Dts.Connections)


{
Dts.Events.FireInformation(1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",

ConnMgr.Name, ConnMgr.ConnectionString), "", 0, ref fireAgain);

try


{
ConnMgr.AcquireConnection(null);

Dts.Events.FireInformation(1, "", String.Format("Connection acquired successfully on '{0}'",

ConnMgr.Name), "", 0, ref fireAgain);


}
catch (Exception ex)


{
Dts.Events.FireError(-1, "", String.Format("Failed to acquire connection to '{0}'. Error Message='{1}'",


ConnMgr.Name, ex.Message),
"", 0);

failure = true;


}

}
if (failure)

Dts.TaskResult = (int)ScriptResults.Failure;

else

Dts.TaskResult = (int)ScriptResults.Success;


}

}

}


The Precedence Constraint Editor for the success flow was set as follows

 

 

 

The Precedence Constraint Editor for the failure was set as follows.

 

 

Note: The connection string of the database was dynamically created . The connection string was stored in a database table which was read by the Execute SQL task : Retrieve hospital list