February 24, 2011 at 12:36 pm
I've created a ssis pkg w/several sql tasks steps. following each step there are two constraints one for success to jump to the next step and the other on failure to send me an email alerting me. I'm getting the email as expected, but I was wondering... in the "Execution Results" tab, there's the actual error that caused the step to fail; how can I possibly include this error to be in the body of the email or as part of an attachment?
Is this possible? If so, I would likely know right away what caused the stop instead of having to look in the pkg itself. Mind you this is still in development phase.
Thx,
John
February 24, 2011 at 3:18 pm
This is what I do. For the component I would trap the OnError event and put a Script task. The script will essentially copy the error message into a user defined variable that I then store in the database. You can read the system variables like so Dim vars As Variables
Dts.VariableDispenser.LockForRead("ErrorCode")
Dts.VariableDispenser.LockForRead("ErrorDescription")
Dts.VariableDispenser.LockForRead("SourceName")
Dts.VariableDispenser.GetVariables(vars)
Try
Dts.Variables("errNumber") = CType(vars("ErrorCode").Value, Integer)
Dts.Variables("errDescription") = vars("ErrorDescription").Value.ToString()
Dts.Variables("errSource") = vars("SourceName").Value.ToString()
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
February 25, 2011 at 12:34 am
I think the easiest option (and the one following best practices) is to add an OnError event handler.
In that event handler, you have access to variables that describe the error, so you can add an Email Task there using those variables.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply