SSIS - how to capture PRINT message?

Question

The Execute SQL task calls a procedure that has a PRINT command inside. How can I display these messages at once in the progress tab (or in output)?

Answer 1

You will need to write your own process for invoking SQL as the Execute SQL Task does not have the event handler defined for the side channel communication.

I would go at it from a Script Task

Setup

In my database, I have a stored procedure defined as the following

CREATE OR ALTER PROCEDURE dbo.Printsalot
AS
BEGIN
    SET NOCOUNT ON;
    RAISERROR('This is the first message', 10, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:05';
    RAISERROR('This is the second message', 10, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:04';
    RAISERROR('This is the third message', 10, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:03';
    RAISERROR('This is the fourth message', 10, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:02';
    RAISERROR('This is the fifth message', 10, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:01';
    RAISERROR('This is the sixth message', 10, 1) WITH NOWAIT;
END;

I use RAISERROR instead of print so I can WITH NOWAIT otherwise, the PRINT isn't going to be so useful. And I put delays in there to simulate a longer running process.

Script Task

Your TODO with the following code

1. Define connString appropriately. I've never had much luck tying into the existing connection managers in a script task so I typically use a C# variable to hold the connection string info and pass that into the script
2. Define a CommandTimeout on your command object if the proc takes too long
3. Define proc appropriately

And code

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_ExecuteSQLWithInfo
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // TODO: Fix this
            string connString = @"SERVER=.\dev2017;Integrated Security=true;DATABASE=tempdb";
            // TODO: Fix this
            string proc = "dbo.Printsalot";
            using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();
                connection.FireInfoMessageEventOnUserErrors = true;
                connection.InfoMessage += new SqlInfoMessageEventHandler(HandleSqlProgress);

                using (SqlCommand command = new SqlCommand(proc, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    // TODO: Define a command.CommandTimeout value
                    command.ExecuteNonQuery();
                }

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

        public void HandleSqlProgress(object sender, SqlInfoMessageEventArgs e)
        {
            bool fireAgain = true;
            Dts.Events.FireInformation(0, "SQL Progress", e.Message, "", 0, ref fireAgain);
        }

    enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

Results

From the output window/Progress window

SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_71109444.dtsx" starting.
Information: 0x0 at SCR Echo Proc, SQL Progress: This is the first message
Information: 0x0 at SCR Echo Proc, SQL Progress: This is the second message
Information: 0x0 at SCR Echo Proc, SQL Progress: This is the third message
Information: 0x0 at SCR Echo Proc, SQL Progress: This is the fourth message
Information: 0x0 at SCR Echo Proc, SQL Progress: This is the fifth message
Information: 0x0 at SCR Echo Proc, SQL Progress: This is the sixth message
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\SO_71109444.dtsx" finished: Success.

References

• http://www.sqlskills.com/blogs/jonathan/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell/
• https://stackoverflow.com/a/349362/181965
• c# - SqlConnection InfoMessage triggering only at end of execution
• https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlinfomessageeventhandler
• https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.infomessage
• https://dba.stackexchange.com/q/54544/2131