Continue SSIS package when One Connection string is not reachable

Question

I have a strange situation. I have an SSIS package and this package takes connection string from a table in SQL server something like (Data Source=XYZ;Initial Catalog=Mail;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;) and there are many more connection string like this.
It uses this connection string to insert records to the desired SQL server. But, whenever a server is not reachable the SSIS package fails and stops execution.

I want to know how we can continue SSIS package if a particular server fails so that it doesn't effect other servers. Any help or comment is appreciated.

billinkc billinkc · Accepted Answer · 2019-09-18T01:35:29

I assume the current package looks something like

Execute SQL Task (Get master list of connection strings)
ForEach (recordset) Enumerator (Assign current connection string to Variable)
Execute SQL Task (Inserts into table)

For each record you find, you assign that to a variable which is then used to drive the ConnectionString property of an OLE DB Connection manager.

Assuming that approximates the problem, you would need to add a precursor step to #3 which tests the validity of the connection string/manager. In SSIS, this mostly commonly be implemented through a Script Task. Rather than deal with Failing the Script Task, I'd also create an SSIS variable called IsConnectionValid and the result of the Script Task will be to set that to True or False depending on the state of the connection.

Psuedologic

Assumes Read only Collection is our variable @[User::ConnectionString]

Assumes Read/Write collection is our variable @[User::IsConnectionValid]

Assumes I can code without opening a text editor but the logic is sound

// re-initialize our state to false
Dts.Variables["User::IsConnectionValid"].Value = false;
// Access the current connection string
string cs = Dts.Variables["User::ConnectionString"].Value.ToString();
try
{
    using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(cs))
    {
        conn.Open();
        // If the connection didn't blow chow, then it's good *right now*
        // The server could fall over in the next instant but this is the best we can do
        Dts.Variables["User::IsConnectionValid"].Value = true;
    }
}
catch(Exception ex)
{
    // Swallow the error
    ;
}

Now that we have this script task running and safely evaluating our connection, then the last step is to change the Precedence Constraint between this new step 3 (script task) and the old step 3 (Execute SQL Task). Double click the connecting line and change it from the current default of OnSuccess to PrecedentAndConstraint (name approximate)

The constraint is simply @[User::IsConnectionValid] and it remains an AND conditional. This means that it will only give the Execute SQL Task the signal to fire if the variable is true and the preceding task didn't fail.

Continue SSIS package when One Connection string is not reachable

1 Answers

Psuedologic