SSIS CRLF in SourceData Field

Question

I've got several flat files that I'm working on loading into SSIS and have hit a major snag I can't seem to fix. The flat files are all column delimited by pipe (|) and each row is delimited by CRLF - HOWEVER; one of the columns is SupplierEmail --> this is user input for multiple ERPs and sometimes there is a CRLF in that field. As you can imagine that shows that and the next line off thus breaking the package...

How do you deal with CRLF in the source data?

Answer 1

The usual way to deal with this is to enclose each columns' value in Quotation marks, and any CRLF that is inside of pair of quotes is part of the data, and any CRLF that is outside of any quotes is a row delimiter.