Capture successful execution print messages from stored proc in C#

Question

Good day !

We have a very big process with calling structure containing 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 is not because the process completes successfully that it is indeed successful. So to troubleshoot, we have added various print messages and counts in the process, so we need to capture them and output the results every time we run the job.

The stored proc is being executed from C# SSIS 2016 script task.

I am not really familiar with SSIS or .net... I am a SQL/SAS programmer, so I am a little stuck with this. I am thinking about using StatementCompletedEventArgs or SqlInfoMessageEventArgs, but whatever I try, I cannot seem to output the prints! Maybe capture them in a variable and then output the result after ?

Please note that English is not my primary language, so let me know if this is not clear enough ;-)

Here is what I have so far :

 public void Main()
            {
                string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
                string LogFile = "\\\\fld6filer\\BrsSystems\\Workitems\\TF19816\\test_" + datetime + ".txt";
                string infoMessageText = "";
                //string par1 = Dts.Variables["InputSurveyGroupCodeId"].Value.ToString();
                //string par2 = Dts.Variables["ReferencePeriod"].Value.ToString();
                //string par3 = Dts.Variables["InputSurveyCodeId"].Value.ToString();
                //string par4 = Dts.Variables["InputISFTable"].Value.ToString();
                //string par5 = Dts.Variables["InputSUFFrameTable"].Value.ToString();
                //string par6 = Dts.Variables["InputExtraVarSUFTable"].Value.ToString();
                //string par7 = Dts.Variables["InputPreviousPseudoFrameTableName"].Value.ToString();
                //string par8 = Dts.Variables["InputPreviousPseudoFrameTableExtra"].Value.ToString();
                //string par9 = Dts.Variables["InputFFTable"].Value.ToString();
                //string par10 = Dts.Variables["DEBUG_MODE"].Value.ToString();

                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);

                    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 += delegate (object sender, SqlInfoMessageEventArgs e)
                    {
                        infoMessageText += e.Message.ToString();
                    };

                    //using (var reader = Storproc.ExecuteReader())
                    //{
                    //    do
                    //    {
                    //        if (reader.Read())
                    //            Console.WriteLine(reader.GetInt32(0));
                    //    }
                    //    while (reader.NextResult());
                    //}

                    //MyConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);

                    MyConnection.Close();

                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                catch (Exception ex)
                {

                    Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);

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

                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }

            static void InfoMessageHandler(object sender, SqlInfoMessageEventArgs e)
            {
                string myMsg = e.Message;
                Console.WriteLine(e.Message);
            }

            //private void OnInfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs args)
            //{
            //    System.Data.SqlClient.SqlError sqlEvent;
            //    foreach (var sqlEvent in args.Errors)
            //        Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, false);
            //}
        }

Thanks in advance for your help. I really appreciate it :-)
Mylene

Answer 1

The approach I've always used is writing the messages and whatever meta data to a table. Then the data is persisted and you get to use SQL to query the table.

Answer 2

I cannot seem to output the prints!

Output to where?
A SSIS script task do not really support a GUI during runtime, because that wouldn't make any sense for a ETL process.

Log the result of the particular stored procedures into a logging table.