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