Developing Matt

My Technical Journal

SSIS: The hidden Error Description and my attempt to CONQUER it

leave a comment »

I’ve set up my SSIS to send my row errors to a table and populate a table with

1.  The offending row (some pertinent information to help me find the row, that is)
2.  The error column (which I haven’t figured out yet, but it’s supposed to give me the offending column)
3.  The error code

So, to find what this error is about you can either figure out what the problem is passively by staring at the row until your eyes get tired or you can get the description of the error by either going to this microsoft msdn page or by going to your hard drive with the error code by:

<skip this section>

1. Converting the ErrorCode to HEX with Calc
2. Remove the preceding F characters
3. Searching C:\Program Files\Microsoft SQL Server\100\SDK\Include\dtsmsg.h

For example my error was -1071607696 which converted to HEX was FFFFFFFFC0209070 so I searched the file for C0209070 and discovered my error to be: "The data value violated the integrity constraints for the column." The error column in the table shows 0 which wasn’t correct, but maybe it will be for you.

</skip this section>

This is nice and handy and incredibly uninteresting an annoying… but today I noticed that when I set up a dataviewer on my RED ARROW that I see an extra column ‘Error Description’ 

WHAT??? 

Why don’t I have access to this description?  I found this great lonely post (credit to Doug Laudenshlager) in a sea of internet flurry.  The post suggests to use a script transformation editor to get this description.  There isn’t much to it.  All in all, one line of code and I am able to get my error description (looks like I had a key violation error). 

In my package, I routed all of my row insert errors to a union all transformation and then called this script to get the description before inserting my error into my error table. 

Here’s the line of code, but I’d recommend looking at that link (and here’s another one that mentions it briefly )

The magical line of code:

Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

Advertisements

Written by matt

April 1, 2010 at 7:00 pm

Posted in SSIS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: