January 30, 2012 at 10:22 am
It's pretty obvious that the GetErrorDescription method is the way to get an error description. But it does not meet my requirements because of several built in restrictions or to put it more succinctly it is that way by design.
1. the GetErrorDescription method is a member of the Microsoft.SqlServer.Pipeline.Wrapper name space and requires the Microsoft.SqlServer.DTSPipelineWrap.dll as a reference. This is not a default reference in the Script Task project but is a default reference in the Script Component task.
2. The method generated that allows access to the method is Input0_ProcessInputRow and takes an Input0Buffer type parameter which I believe is defined in the dll referenced above. It also has the delightful ability to reference the ErrorCode required as a parameter by the GetErrorDescription method. However it is invoked for every failed row and I want to get it once only.
3. The script component is available in the Data Flow toolbox but not in the Control Flow toolbox or in the Event Handler toolbox because Script Component is a Data Flow Transformations component. This is pretty sensible
What this boils down to is that if you want to get the ErrorDescription you can do it but ONLY in the Data Flow. Which begs the question if I have a number of SQL Tasks (which I do) and want to handle errors in them how do I get the ErrorDescription? Is this a case of me trying to eat soup with a fork? :angry:
One would think there would be a way to get the ErrorDescription separate from the Row and pipeline or in addition to the Row and pipeline. I'd also take umbrage with the requirement for a method at all since the ErrorCode is provided why not include the ErrorDescription to the object (most Error objects in C# and .NET have both a code and message)?
If anyone has found a way out of this locked room I would certainly appreciate hearing about it.
Joshua CodeMonkey
January 31, 2012 at 1:06 am
It's there, just not where you'd expect it 😀
You need to create an event handler on the task you want to monitor. In the event handler, there is a system variable @ErrorDescription that you can use (see screenshot in attachment).
(sometimes in a locked room, you don't need to open the door, but the window 😀 ;-))
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 9:58 am
Indeed you are correct. I am probably just complaining to complain but it is a habit with MS software. So ... my bad. I have a link with a functional example of what you speak of http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/. Thank you for getting back to me.
January 31, 2012 at 10:51 am
benavraham50 (1/31/2012)
I am probably just complaining to complain but it is a habit with MS software.
It's understandable. The solution is sometimes very well hidden.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply