Capturing the error for a query written in an Execute SQL task
Hi.
I have a SSIS 2005 pkg with an execute SQL task and a query as a SQLstatement. I want to capture a possible error generated from this query. How can I do it? Using the Result set?
Thanks
October 28th, 2010 10:32am
You will need to add error handling to the task.
There is a blog entry related to it here:
http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx
Sethhttp://lqqsql.wordpress.com
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 10:51am
If you just want to store it in a global string variable, you can create an OnError Event Handler at the package level or the level of the step with a Script task that does something like this:
Dts.Variables["MyErrorText"].Value = Dts.Variables["System::ErrorDescription"].Value.ToString();
If you want the error code, it is:
Dts.Variables["MyErrorCode"].Value = Dts.Variables["System::ErrorCode"].Value.ToString();
One problem is that System::ErrorDescription is not available in all Event Handler scopes. See
http://msdn.microsoft.com/en-us/library/ms141788.aspx for a list of which variables are in each scope.
October 28th, 2010 11:31am