I'm working in BIDS (SSIS 2008). I have a very simple data flow task that imports a flat file into an OLE DB table. The flat file has 10 columns, and they all map into the corresponding columns in the table. The flat file column delimiter is the pipe character, the row delimiter is {CR}{LF}, and it doesn't have headers. On the flat file source, I re-direct the offending rows (for all 10 columns) on both Error and Truncation to an error output flat file.
I run this data flow task every day, but I can't guarantee that the flat file will always have 10 columns for every record. So, because SSIS has the bad habit (at least, in my view) of using the row-delimiter as the column-delimiter for the last-defined column, then it tries to merge rows. This means that if I have an insufficient number of columns, then not all rows will be imported as separate rows in the table. It also means that offending rows won't necessarily write to the error output flat file.
For the time being, the only 2 scenarios are (1) the file will have the desired number of columns and (2) the file will be deficient in the number of columns. I have yet to see a file with more columns than expected. Because this file is used in my unit testing process, I don't want to pre-process the files to have the correct number of columns. I want the offending rows to write to the error output flat file (or, if not the error output flat file), at least to some other flat file and not get imported. I still want the rows with the correct number of columns to import. How can I achieve this.
