November 14, 2011 at 9:26 pm
Hi all,
I have a SSIS task executing a SQL stored procedure. The sproc has a condition that should be classified as a warning. I am looking for SSIS to detect the warning condition and state the warning in its output, as it would when it encounters internal warnings when it runs.
My sproc uses raiserror within with a severity of 10, as such:
RAISERROR(@msg, 10,1) (note: the proc doesn't exit at the point of warning, but it raises it mid-way through the proc execution.)
In SSIS, I set up an OnWarning Event Handler for the task, that logs to a table whenever OnWarning is identified.
The warning is never flagged by SSIS. I have confirmed separately that the warning is in fact being identified by the sproc.
How does one get SSIS to identify warning conditions?
Thanks in advance...
November 16, 2011 at 3:59 am
I don't think this is possible in SSIS.
The warnings that SSIS outputs are internal ones.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2011 at 4:17 am
If the stored procedure is raising an error, would the OnError event handler work, instead of OnWarning?
John
November 16, 2011 at 5:16 am
John Mitchell-245523 (11/16/2011)
If the stored procedure is raising an error, would the OnError event handler work, instead of OnWarning?John
Yes, if the severity level is high enough.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2011 at 7:22 am
OK, I see - severity up to 10 is information or warning; 11 or greater is error. Have you tried an onInformation event handler instead? If that doesn't work, you could try altering your stored procedure so that it generates a return code, and output that to a variable.
John
November 16, 2011 at 12:47 pm
Thanks, all, for the responses. I am going to try the OnInformation event as suggested. I cannot try the exit code approach, since I believe that will be perceived as a non-zero error, which I don't want. I'll report back on the OnInformation approach later this evening when I have a chance to test it.
Thanks again!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply