SQL消息输出在SSIS中显示为错误(SQL Messages Output appearing as Errors in SSIS)

SQL消息输出在SSIS中显示为错误(SQL Messages Output appearing as Errors in SSIS)

因此，无论何时查询执行失败，我的SSIS包都会将查询执行中的消息记录为错误。 因此，例如我的查询有2个Print语句，由于某些原因查询因某些错误而失败。 SSIS记录所有2个打印语句以及实际错误。 我不希望SSIS包将打印消息记录为错误。 我的包正在读取有关“OnError”事件处理程序的信息。 并且日志的源设置为：System :: ErrorDescription。 请考虑以下查询：

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'

以下是SQL Management Studio的输出/消息。 最后一行是实际错误，其余行都是打印语句。

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.**

但是当我尝试执行SQL任务并运行此查询时，SSIS中的OnError事件处理程序将来自消息窗口的所有这三行视为多个错误。

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.

最满意答案

起初，我怀疑你实际上是用“RAISERROR”“打印”你的陈述，这是一种相当普遍的做法。

但是，在进一步的研究中，我发现PRINT命令似乎是标准行为，它包含在由同一脚本中的后续RAISERROR发送给调用应用程序的错误消息中。

因此，如果您不在脚本中使用RAISERROR，则SSIS不会将PRINT语句视为错误。 但是如果你确实使用了RAISERROR，那么在RAISERROR之前发生的任何PRINT语句都将包含在引发的错误消息中。

它很奇怪而且远非直观，但它似乎是标准行为，看起来你只需要解决它。

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.