Ignore last/corrupted record from flat file source

问题:

I have following csv file:

col1, col2, col3
"r1", "r2", "r3"
"r11", "r22", "r33"
"totals","","",

followed by 2 blank lines. The import is failing as there is extra comma at the end of the last data row and most probably will fail because of the extra blank lines at the end.

Can I skip the last row somehow or even better stop import when I get into that row? It always has "totals" string in the "col1".

UPDATE:

As far as I understood from the answers that it is not possible to do that with Flat File. Currently I did that with the "Script Component" as a source

回答1:

You can do it by reading the row as a single string.

Conditionally split out Null and left(col0)=="total"

in script component you then use split function

finally trim("\"")

回答2:

I know of nothing built-in to SSIS that lets you ignore the LAST line of a CSV.

One way to handle this is to precede your dataflow with a script task that uses the FileSystemObject to edit the CSV and remove the last line.

回答3:

You will need to create a custom script where you read all lines but the last within SSIS.