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

CodePudding user response：

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

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
Define a CommandTimeout on your command object if the proc takes too long
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.

Setup

Script Task

Results

References