Reading a text file with SSIS with CRLF or LF

Question

Running into an issue where I receive a text file that has LF's as the EOL. Sometimes they send the file with CRLF's as the EOL. Does anyone have any good ideas on how I can make SSIS use either one as the EOL?

It's a very easy convert operation with notepad++ to change it to what ever I need, however, it's manual and I want it to be automatic.

Thanks,

EDIT. I fixed it (but not perfect) by using Swiss File Knife before the dataflow.

Answer 1

If the line terminators are always one or the other, I'd suggest setting up 2 File Connection Managers, one with the "CRLF" row delimiter, and the other with the "LF" row delimiter.

Then, create a boolean package variable (something like @IsCrLf) and scope this to your package. Make the first step in your SSIS package a Script Task, in which you read in a file stream, and attempt to discover what the line terminator is (based on what you find in the stream). Set the value of your variable accordingly.

Then, after the Script Task in your Control Flow, create 2 separate Data Flows (one for each File Connection Manager) and use a Precedence Constraint set to "Expression and Constraint" on the connectors to specify which Data Flow to use, depending on the value of the @IsCrLf variable.

Example of the suggested Control Flow below.

Answer 2

how about a derived column with the REPLACE operation after your file source to change the CRLFs to LFs?