Execute SQL Task in SSIS cannot show the PRINT Statement outputs from a stored proc

  • Article

If you have a stored proc that has some print statements inside it and you execute it using Execute SQL Task of SSIS, the package execution log or the Packge event log will not report those print statements.   For ex.

1. You create a simple stored proc in SQL Server like this:
CREATE PROC TEST_SP AS
PRINT ‘TEST MESSAGE’
GO
2. Now create a new SSIS package with an Execute SQL Task.
3. Set the Execute SQL Task to execute above SP.
4. Execute the package and you will see that the 'TEST MESSAGE' is neither reported in the standard DTExec output nor inside the package log.

Apparently the PRINT statements are returned by using a special resultset and the Execute SQL Task is not able handle it.

What are our options:

  • Change the print statements inside the SP to have a string variable being filled with those messages (of the print statements). At the end of the SP, just return this string variable. We can then capture it from SSIS in a variable for pasting in the SSIS package log.
  • Change the print statements inside the SP to put all the print messages into a temporary table. Then we can have a small data flow task, that can read that data and paste it inside SSIS package log.
  • Write a script task to capture the messages from the stored procedure and then we can write the messages to the log file.