SSIS Fault Tolerant Data Connection

Question

I feel like a may be standing too close to this one. I've inherited an SSIS package that has two data connection objects. On OLE DB connection (to perform work) and an ADO.NET connection (to a different server) for logging.

The server on the far end of the ADO.NET connection can be flaky and I need to make the logging fault tolerant. The logging task is already set not to fail the package, nor the parent, on failure. However the process isn't getting that far. The package fails because the connection fails, not because the task fails.

The logic path of this package is Log Start > Do Stuff > Log Completion. In this particular example Log Start succeeded, as did Do Stuff. Log Completion failed because the server was suddenly unavailable.

The error message at SSIS report level is

Log Status Completion Error: Failed to acquire connection 'Logging ADO NET'. Connection may not be configured correctly or you may not have the right permissions pm this connection.

All suggestions are welcome. (Thanks and Merry Christmas)

Answer 1

Not sure if this is a solution or a work-around. Either way, it solved the problem. I disabled the SQL Task and created a Script Task. The connection happens within a try/catch. If it fails, the flow goes to a "do-nothing" catch block. The package sees the completion as success and progress continues. A screen image of the new Script Task and its code are included.

#region Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_4ddaab95fb4d46ef9521d651f5227575
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : 
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
    public void Main()
    {
        Package pkg = new Package();
        Variables vars = null;
        VariableDispenser vd = pkg.VariableDispenser;
        SqlConnection LoggingConn;
        SqlCommand LoggingCommand;
        vd.GetVariables(ref vars);
        try
        {
            LoggingConn = (SqlConnection)Dts.Connections["Logging ADO NET"].AcquireConnection(null);
            LoggingCommand = new SqlCommand("[etl].[usp_LogPackageStatus]", LoggingConn);
            LoggingCommand.CommandType = CommandType.StoredProcedure;
            LoggingCommand.Parameters.AddWithValue("@PackageID", vars["System::PackageID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@PackageName", vars["System::PackageName"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@MasterExecutionID", vars["$Package::MasterExecutionID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@ExecutionID", vars["System::ExecutionInstanceGUID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@Status", vars["User::Status"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@StartTime", vars["User::StartTime"].Value.ToString());
            LoggingCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //Do nothing

            //For Debugging
            //MessageBox.Show(ex.Message);
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion
}

}