CODEWITHSUNDEEP
ssis
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Ignore last/corrupted record from flat file source in SSIS

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

Upvotes: 0

Views: 1137

Answers (4)

gwgeller
gwgeller

Reputation: 247

This is old but it came up for me when searching this topic. My solution was to redirect rows on the destination. The last row is redirected instead of failing and the job completes. Of course you will potentially redirect rows you don't want to. It all depends on how much you can trust the data.

Upvotes: 1

KeithL
KeithL

Reputation: 5594

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("\"")

Upvotes: 2

a415
a415

Reputation: 369

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

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

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.

Upvotes: 1

Related Questions