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

Question

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.

@MChalut check out this answer: https://stackoverflow.com/questions/71109444/ssis-how-to-capture-print-message — Hadi, Feb 14 '22 at 18:54

Hadi · Answer 1 · 2022-02-08T20:08:09.690

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:

Return a message from a stored procedure to C# app

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

Accessing InfoMessages for SQL Server queries in C#

I get exactly what I want when it fails!! I want to output the print messages also when the execution succeeds ! — MChalut, Feb 08 '22 at 02:15
@MChalut then you should define a function. I edited my code, check it out — Hadi, Feb 08 '22 at 05:45
This is a very big process. Calling structure contains about 30 stored procs. Clients are sending us millions of records that needs to go through many rules depending on many criteria. That being said, it not because the process completes successfully that it is indeed successful. There are various print messages and counts in the process, so we need to capture them and output the results every time we run the job. — MChalut, Feb 08 '22 at 11:34
I am not really familiar with SSIS or .net... I am a SQL/SAS programmer, so I am a little stuck with this. I understood that there are no events that are being triggered in the script that I wrote. I was thinking maybe using StatementCompletedEventArgs but I cannot seem to output the prints that way. Maybe capture them in a variable and then output the result after ? — MChalut, Feb 08 '22 at 11:38
Please note that English is not my primary language, so let me know if this is not clear enough ;-) — MChalut, Feb 08 '22 at 11:40
@MChalut I added some links to the answer: I think you should use `InfoMessage` properly — Hadi, Feb 08 '22 at 20:11
From what I can see, SqlInfoMessageEventArgs Message { get } Gets the full text of the error sent from the database. That's not what I am trying to do, but thanks anyway... — MChalut, Feb 09 '22 at 10:49

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

1 Answers1

Update 1