Log Errors but Continue to Load Data?

Hello,

In my SSIS package I am trying to import a .csv flat file with about 170,000 rows of data with 75 columns (I know this is rather large). I would like to create a SSIS package that loads ALL of the rows of data into a table. This table has it's fields defined (such as money, float, varchar, datetime, etc). I want the data to load to this table even if there is a conversion error converting any field. When the data is loaded to this table, any fields that couldn't be converted should be set to null. Of the 75 columns of data, there are MANY columns that could be invalid.

For example, if the flat file contains the value "00/00/00" for my "PaidDate" field, I would want all of the other fields to be populated and the value for this particular record's "PaidDate" field to be null.

It would also be nice if I could trap any of the fields that caused a problem and log them along with the row.

I know that SSIS supports the "Redirect Row" method for handling data, but in my case, I don't want to redirect it. I want to continue to load it, just with a null value.

Is there an easy way of doing this (i.e. perhaps by using the Advanced Editor for the Flat File Source and setting something in the Input/Output columns)? I really don't want to create a Derived Column or Data Conversion transformation for every field that needs to be converted (b/c there are 75 columns to maintain this for).

I know if I import this file to an Access 2003 database, it imports the data fine and logs any conversion errors to an "_ConversionErrors" table. I am basically looking for this same behavior in SSIS, without having to maintain 75 columns.

TIA

There is one other way I can think of. If you change the output columns of your source to be the same data type as your destination and select "ignore" for all errors then it will just cause that one column of the row to not be loaded into the data flow. Of course you have no way of determining which rows these are and you also have to manage the metadata by hand so I doubt you'd be much better off than a data conversion transformation.|||

Thanks Brent!

I actually started going down this path (convert from the flat file source) right after I posted my initial question. It stinks that I can't identify the rows. I tried to do make the conversion/truncation for each "redirect" to another route and then join them all back together using the UNION ALL, but it turns out you only get three values back from the ErrorOutput columns...A stream reader, an error code, and an error column. This isn't enough information to get back to the row that caused the error without some nasty parsing logic I assume.

Would be nice if there was a way to funnel that stream and re-create a row from it!

Thanks again!