sql server - SSIS reading LF as terminator when its set as CRLF

Question

using SSIS 2012. My flat file connection manager I have a delimited file where the row delimiter is set to CRLF, but when it processes the file, I have a text column that has an LF in it. This is causing it to read that as a row terminator causing it fail. Any ideas?

Answer 1

I have no SSIS experience but as an ETL developer I have faced this many times. So my suggestions might not help you solve the problem but hopefully point you in the right direction

• If the problem field has text qualifier (single or double quote usually) and SSIS supports use it
• Also if there is an option to force SSIS to use different end of record delimiter other than LF (CRLF in this case) I'd use it (hopefully there is no CRLF in the problem field text)
• If the problem field is not the last field, you can count the number of de-limiters by reading the entire record as a single LF delimited field to identify and filter out the problem records (if they are only few) and try to stitch them back
• If possible read the file as single record (if SSIS has an option) and replace all LF, provided CR is consistent end of record delimiter from the source