So my SSIS Package logs messages from the query execution as errors whenever query execution fails. So for instance my query has 2 Print statements and for some reason query fails because of some error. SSIS is logging all the 2 print statements along with the actual error. I don't want SSIS package to log print messages as Errors. My package is reading this information on "OnError" event handler. and the source of the logs is set to : System::ErrorDescription. Consider the following query:
PRINT 'Trying to set string value to a datetime column.'
PRINT 'So i should get this error: Conversion failed when converting date and/or time from character string.'
UPDATE TempTable SET CreateDateTime = 'StringValue'
Below is the output/Message from SQL Management Studio. Last line being the actual error, and remaining lines are all Print Statements.
Trying to set string value to a datetime column.
So i should get this error: Conversion failed when converting date and/or time from character string.
**Msg 3930, Level 16, State 1, Procedure pr_TempTable, Line 3 Conversion failed when converting date and/or time from character string.**
But all these three lines from messages window is treated as multiple errors by OnError event handler in SSIS when i try to execute SQL task and run this query.
At first, I suspected that you were actually "Print"-ing your statements with RAISERROR, which is a fairly common practice.
However, on further research, I found that it seems to be standard behavior for PRINT commands to be included in the error message that is sent to a calling application by a subsequent RAISERROR in the same script.
So if you don't use RAISERROR in your script, your PRINT statements will not be treated as errors by SSIS. But if you do use RAISERROR, any PRINT statements that occurred before the RAISERROR will be included in the error message that gets raised.
It's weird and far from intuitive, but it seems to be the standard behavior and it looks like you'll just have to work around it.