Decoding Decimal Form of HRESULT from ErrorCode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to another OLE-DB Command transformation that logs the error on that row to a "failed rows" table. In this table I log the ErrorCode and ErrorColumn values that come with the error path from the first OLE-DB Command.

OK, that's all working great. However, here's the kicker: there's no error description value. The ErrorCode value, naturally, is the decimal form of an HRESULT--for example, -1071607696. Without some further information, however, this code is not useful for troubleshooting.

Has anyone figured out a trick here? I'm not even certain that this is an SSIS HRESULT, since it could for all I know be from the OLE-DB layer, the database layer, or somewhere else.

Thanks,
Dan

http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0209070.html

The DescribeRedirectedErrorCode method may also be of some use here. Not sure what you actually expect to do, but normally I would log the full error via another means, such as the built in SSIS logging. Use that for the text description. The code would alllow you to automate handling of different error scenarios.

|||Hi Darren,

Thanks for the link to that error info (I had actually found that subsequent to my original post with some additional searching), and for the pointer to DescribeRedirectedErrorCode. I did not know about the existence of this method. It's also interesting to find out that this is an SSIS HRESULT even though the error pertains to a foreign key constraint violation in the database layer--is SSIS re-interpreting the original SQL Server exception? I wonder whether an ErrorCode will always be a native SSIS error code...?

You refer to logging the full error via another means. I get the feeling that I'm missing an opportunity here to be logging a row-level error in a data flow in a different way than I am now. I'd obviously prefer to log the full error info instead of just ErrorCode and ErrorColumn. However, I don't see how this would work.

Do you redirect the row first through a script component so that you can programatically use the ErrorCode to call DescribeRedirectedErrorCode for additional info for the subsequent logging?

Or are you catching the error in the control of flow? How does that work exactly? Does a row-level exception in a data flow fire an error event at the control of flow level? I guess I was specifically trying to prevent that by using Redirect Row from my OLE-DB Command transform--I just want to log the problem with that row and keep moving through the rest of the rows...

Thanks,
Dan

|||

You may also be interested in taking a look at "Enhancing an Error Output with the Script Component," which was new in the December drop of BOL.

Also, be aware of the "Integration Services Error and Message Reference" list which includes the HRESULT in hex. As for converting (in code), although I don't have the code that I used to create the list in front of me right now, I believe there are format specifiers that you can use with .ToString() to convert quite simply between decimal and hexadecimal representations.

-Doug

|||That was exactly what I needed, Douglas, thank you. I am going to use that trick on future error pathways. Hopefully a future release of SSIS will make this unnecessary by adding an intrinsic ErrorDescription column to go along with ErrorCode and ErrorColumn.

Here is the link for those who'd like to read the article:

http://msdn2.microsoft.com/en-us/library/ms345163.aspx

Has anyone else noticed that Google's URLs pointing to MSDN articles have a "(d=robot)" in them, so that when you click from Google to MSDN the article shows up with no styling or sidebar navigation. Example:

http://msdn2.microsoft.com/en-us/library(d=robot)/ms345163.aspx

I've noticed it doing this the last couple days.

Thanks again,
Dan

|||

Doug,

What is the difference between GetErrorDescription and DescribeRedirectedErrorCode, they both seem remarkably similar, apart from the hosting class. Context maybe?

How does using GetErrorDescription like this know about the upstream component that raised the error? Surely it needs to know, since if as a component author I generate my own error codes, I would then override DescribeRedirectedErrorCode to give you the description, but how do you call my implementation?

|||

Darren,

A complete answer will need to come from the dev team. The methods seem to do the same thing, as you observed - get a description from an error code. I suspect that this works only with Integration Services errors and messages, and that it is made possible (or easier) by the fact that all of these are consolidated in the managed Microsoft.SqlServer.Dts.Runtime.HResults class. I'll see what I can find out.

-Doug

|||

You can also use my enhanced error component to add the column name of the column that failed to the error output.

I guess I should add the error description as well