Capturing PRINT Statements Embedded in T-SQL Stored Procedures

SSIS Stuff - 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.

...

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

..."

I like to pepper PRINT statements in my T-SQL, using them for informational/feedback messages. So this post caught my eye...

The SqlInfoMessageEventHandler & InfoMessage nugget is one that I'll want to remember.

One thing to remember, PRINT statements are batched and are not sent to the output/client immediately. If you need an immediate message sent, use a raiserrror with a severity <= 10 and the "WITH NOWAIT" option.