Capture PRINT messages from a stored procedure

I recently helped with a customer issue where they had a long running stored procedure which output status messages periodically using PRINT statements. They wanted to capture these statements and output them into the SSIS log. Unfortunately, the Execute SQL Task doesn't support this (it's something we're considering for the future), but it's fairly easy to do through a script task.

Our stored procedure:

   2: AS
   3: BEGIN
   4:     print 'very important status information...'
   5: END
   6: GO

Our script:

   1: Public Sub Main()
   2:     Dim conn As New SqlConnection("server=(local);Integrated Security=SSPI;database=Test")
   4:     AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
   6:     conn.Open()
   8:     Dim cmd As New SqlCommand()
   9:     cmd.Connection = conn
  10:     cmd.CommandType = CommandType.StoredProcedure
  11:     cmd.CommandText = "[SPWithPrint]"
  13:     cmd.ExecuteNonQuery()
  15:     conn.Close()
  17:     Dts.TaskResult = Dts.Results.Success
  19: End Sub
  21: Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs)
  22:     Dim sqlEvent As System.Data.SqlClient.SqlError
  23:     For Each sqlEvent In args.Errors
  24:         Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, False)
  25:     Next
  26: End Sub

The print statements return the messages as InfoMessage events, which we catch with our handler and turn into SSIS information events.

When we run the package, we can see the message from the stored procedure in our progress window...


We're looking into adding this functionality to the Execute SQL Task as well, but hopefully this is an acceptable alternative until then.

Comments (8)

  1. johnny_moreno says:

    OK..I attempted this, but…the script never finishes..When I execute the stored proc from query analyzer it takes about 30 minutes, when I attempt the above, the task never finishes, and i never get any print statements. Any ideas?

    By the way, the same behavior occurs when I execute the stored proc using an Execute SQL Task (the task never finishes)…


  2. Hi Johnny,

    I’d suggest running SQL Profiler when executing the package to see whats happening. You should be able to get an idea of what’s going on, and where it’s hanging.


  3. johnny_moreno says:


    Thanks for your reply…I got the script to run, but the print statements didn’t come up until after the stored procedure finished (basically, after the script completed). What am I doing wrong?

  4. I don’t think you’re doing anything wrong – I think this is the standard behavior for the SqlInfoMessageEventHandler. At first I thought it was a limitation with the VSA scripting, but if I run the same code from a VB.Net console app, it behaves the same way.

  5. johnny_moreno says:

    Ok…thank you..I guess there’s no way to get messages "as they happen" – that would be the most ideal – Something for MSFT to think about, I guess…

  6. jocker says:

    what type of Dts ??

      Dts.TaskResult = Dts.Results.Success

  7. Mitoy75 says:

    Any updates on this? Just curious…

  8. Ranjan Gupta says:

    Thank you Matt. the .Net code works perfectly fine, just changed Dts.TaskResult = ScriptResults.Success instead of Dts.TaskResult = Dts.Results.Success.

    Added the following namespaces:

    Imports System.IO

    Imports System.Data.SqlClient

    Have to enable logging to get those messages from the script task  to the table in sql server

Skip to main content