Capture stored proc's print messages from C# SSIS Script task (2016) when completed successfully

user_6722351 0
Viewed 245

I am trying to output the print messages from a stored proc that is being execute from SSIS script task. Here is what I have so far:

public event SqlInfoMessageEventHandler InfoMessage;
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
    string LogFile = "\\fld6filer\\BrsSystems\\Workitems\\TF19816\test_" + datetime + ".log";
        /*Dts.Variables[""].Value.ToString() + "\\" + Dts.Variables[""].Value.ToString() + "_" + datetime + ".log";*/
    using (StreamWriter writer = new StreamWriter(LogFile))
    {
        writer.Write(e.Message);
    }
}

public void Main()
{
    string par1 = "26";
    string par2 = "202111";
    string par3 = "09";
    string par4 = "tblISF09202111";
    string par5 = "tblSUFGenericPseudo202111";
    string par6 = "tblSUFGenericPseudo202111";
    string par7 = "tblSUFGenericPseudo202111";
    string par8 = "tblSUFGenericPseudo202111";
    string par9 = "tblSUFGenericFF202111";
    string par10 = "1";

    try
    {
        //USE ADO.NET Connection from SSIS Package to get data from table
        SqlConnection MyConnection = new SqlConnection();
        MyConnection = (SqlConnection)Dts.Connections["ADODB"].AcquireConnection(null);

        //MyConnection.Open();

        SqlCommand Storproc = new SqlCommand();
        Storproc.Connection = MyConnection;
        Storproc.CommandTimeout = 7200;
        Storproc.CommandType = CommandType.StoredProcedure;
        Storproc.CommandText = "[SurveyInterface].[uspCEProcessingMainScriptAllProcesses]";
        SqlParameter Parameter1 = new SqlParameter("@InputSurveyGroupCodeId", par1);
        SqlParameter Parameter2 = new SqlParameter("@InputReferencePeriod", par2);
        SqlParameter Parameter3 = new SqlParameter("@InputSurveyCodeId", par3);
        SqlParameter Parameter4 = new SqlParameter("@InputISFTable", par4);
        SqlParameter Parameter5 = new SqlParameter("@InputSUFFrameTable", par5);
        SqlParameter Parameter6 = new SqlParameter("@InputExtraVarSUFTable", par6);
        SqlParameter Parameter7 = new SqlParameter("@InputPreviousPseudoFrameTableName", par7);
        SqlParameter Parameter8 = new SqlParameter("@InputPreviousPseudoFrameTableExtra", par8);
        SqlParameter Parameter9 = new SqlParameter("@InputFFTable", par9);
        SqlParameter Parameter10 = new SqlParameter("@DEBUG_MODE", par10);
        Storproc.Parameters.Add(Parameter1);
        Storproc.Parameters.Add(Parameter2);
        Storproc.Parameters.Add(Parameter3);
        Storproc.Parameters.Add(Parameter4);
        Storproc.Parameters.Add(Parameter5);
        Storproc.Parameters.Add(Parameter6);
        Storproc.Parameters.Add(Parameter7);
        Storproc.Parameters.Add(Parameter8);
        Storproc.Parameters.Add(Parameter9);
        Storproc.Parameters.Add(Parameter10);

        Storproc.ExecuteNonQuery();

        MyConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);                
        
        MyConnection.Close();

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

I don't know why, but when the execution fails, I get all I want in the execution results tab (From Visual Studio), but I don't get anything in my output file. The file is not even being created.

script-task etl ssis sql-server c#
1 Answers

Why not simply moving the write log code into the Catch block. myConnection_InfoMessage is meaningless in your case:

public void WriteToLog(string message)
{
    string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
    string LogFile = "\\fld6filer\\BrsSystems\\Workitems\\TF19816\test_" + datetime + ".log";

    using (StreamWriter writer = new StreamWriter(LogFile))
    {
        writer.Write(message);
    }
}

public void Main()
{
    string par1 = "26";
    string par2 = "202111";
    string par3 = "09";
    string par4 = "tblISF09202111";
    string par5 = "tblSUFGenericPseudo202111";
    string par6 = "tblSUFGenericPseudo202111";
    string par7 = "tblSUFGenericPseudo202111";
    string par8 = "tblSUFGenericPseudo202111";
    string par9 = "tblSUFGenericFF202111";
    string par10 = "1";

    try
    {
        //USE ADO.NET Connection from SSIS Package to get data from table
        SqlConnection MyConnection = new SqlConnection();
        MyConnection = (SqlConnection)Dts.Connections["ADODB"].AcquireConnection(null);

        //MyConnection.Open();

        SqlCommand Storproc = new SqlCommand();
        Storproc.Connection = MyConnection;
        Storproc.CommandTimeout = 7200;
        Storproc.CommandType = CommandType.StoredProcedure;
        Storproc.CommandText = "[SurveyInterface].[uspCEProcessingMainScriptAllProcesses]";
        SqlParameter Parameter1 = new SqlParameter("@InputSurveyGroupCodeId", par1);
        SqlParameter Parameter2 = new SqlParameter("@InputReferencePeriod", par2);
        SqlParameter Parameter3 = new SqlParameter("@InputSurveyCodeId", par3);
        SqlParameter Parameter4 = new SqlParameter("@InputISFTable", par4);
        SqlParameter Parameter5 = new SqlParameter("@InputSUFFrameTable", par5);
        SqlParameter Parameter6 = new SqlParameter("@InputExtraVarSUFTable", par6);
        SqlParameter Parameter7 = new SqlParameter("@InputPreviousPseudoFrameTableName", par7);
        SqlParameter Parameter8 = new SqlParameter("@InputPreviousPseudoFrameTableExtra", par8);
        SqlParameter Parameter9 = new SqlParameter("@InputFFTable", par9);
        SqlParameter Parameter10 = new SqlParameter("@DEBUG_MODE", par10);
        Storproc.Parameters.Add(Parameter1);
        Storproc.Parameters.Add(Parameter2);
        Storproc.Parameters.Add(Parameter3);
        Storproc.Parameters.Add(Parameter4);
        Storproc.Parameters.Add(Parameter5);
        Storproc.Parameters.Add(Parameter6);
        Storproc.Parameters.Add(Parameter7);
        Storproc.Parameters.Add(Parameter8);
        Storproc.Parameters.Add(Parameter9);
        Storproc.Parameters.Add(Parameter10);

        Storproc.ExecuteNonQuery();

        WriteToLog("Stored procedure executed successfully");                
        
        MyConnection.Close();

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        WriteToLog(ex.Message);
        Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

Update 1

If you are looking to read output messages from the stored procedure, you can alter the stored procedure and add an output parameter to send the message to the C# script:

If this is not allowed, try using the SqlConnection.InfoMessage properly:

user_70312300
Related