SSIS Flat File - CSV formatting not working for multi-line fileds

2
votes

I want to import *.csv file. There is head row with the column names, and data rows below. The problem is in column description.This column has multi-line text and each line is recognised as the record.

Document has {CR}{LF} for the end of row, and {LF} for the end of line in multi-line text. Like this:

(0)"Name","Description" {CR}{LF}
(1)"John","adsaddsadas" {CR}{LF}
(2)"Mike","dasdsadsdsda
dsadadsdasdsa {LF}
dsadadsadsad {LF}
dasdsadsadsd"{CR}{LF}
(3)"Dave","dsada"{CR}{LF}

It returns an error saying the row (2) is truncated and is missing data

I have selected {CR}{LF} as delimiter, but it still recognises this as 6 records instead of 3, i suppose it, for some reason, recognises {LF} as row delimiter.

Is there anyone who had similar issue here, or knows how to get over this.

Also i want to mention i don't have a lot of experience with this, so i don't know if there is data missing.

1
sql-servercsvssisetl

1 Answers

2
votes

In your flat file connection manager make sure that the Header Row Delimiter is set to {CR}{LF}

enter image description here

And That the Row Delimiter is also set to {CR}{LF}

enter image description here

And Check that Description Column has a length of 4000 (to prevent text from getting truncated) and that the last column delimiter is {CR}{LF}

enter image description here