error output - ignore just one column?

When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....

Have a table with 5 columns

col1 int null,

col2 int null,

col3 char(3) null,

col4 bit null,

col5 int null

My data flow loads data from a flat file and has a record that looks like this

1 5 ABC R 3

I want the row to make it to the destination as follows....

1 5 ABC NULL 3

Then the offending data needs to go over to my error table

err# errcolumn errdata errdesc

1 col4 R Could not convert "R" to bit data type

Any way to do this?

You could create a custom component, or a script component. Otherwise, you'd need to redirect the row, and use a multicast to send it to the error destination and put the row back into the main flow, after correcting the data.|||And even though you've redirected the row, you can still just operate on one column. The problem is knowing which column is in error.|||

Yes - this is the problem - the users who review the etl loads want to see: "You had invalid data in the BirthDate column". They do not want to see - "You had invalid data somewhere on record # 26".

I really want to stay inside SSIS to do these data type checks, but I'm leaning towards shoving everything into the engine & using isdate(), isnumeric(), etc. to get the flagrant data conversion stuff cleaned up. Then bring it back into SSIS to do lookups & "softer" validations. My real table (unlike my example) has 50+ columns to do checks on, and I don't want 50 branches or more in my SSIS package, all with error flows, and unions everywhere.

Is there any way of keeping this in SSI S, using the Data conversion/Derived Column transforms, and capturing column specific errors, instead of generic row errors?

|||Take a look at this post - it may have an answer for you - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509836&SiteID=1|||I saw that yesterday - it looked promising, but I'm relatively sure that the folks who run the servers here won't allow anything other than the Microsoft released stuff onto the servers. I'm toying with a workaround right now that hopefully will work........I'll check back in.