Reading a text file using SSIS with CRLF or LF

Question

Running the problem when I receive a text file with LF as EOL. Sometimes they send a file with CRLF as EOL. Does anyone have any good ideas on how I can get SSIS to use one of them as EOL?

This is a very simple conversion operation using notepad ++ to change it to what I need, however, this is a guide and I want it to be automatic.

Thanks,

EDIT. I fixed it (but not perfect) using a Swiss file knife before the data stream.

Answer 1

I am for the second post of the vote for the Swiss file knife.

To integrate this, I had to add the Execute execution task:

However, I have a bunch of packages that run For-Every-File loops, so I needed BIML - maybe this will help the next soul.

<ExecuteProcess Name="(EXE) Convert crlf for <#= tableName #>" Executable="<#= myExeFolder #>sfk.exe"> <Expressions> <Expression PropertyName="Arguments"> "crlf-to-lf " + @[User::sFullFilePath] </Expression> </Expressions> </ExecuteProcess> 

Answer 2

If line terminators are always one or the other, I would suggest setting up 2 file connection managers, one with a CRLF line separator and the other with an LF line separator.

Then create a variable of type boolean (something like @IsCrLf) and apply it to your package. Take the first step in your SSIS Script package The task in which you read in a file stream and try to figure out what the line terminator means (based on what you find in the stream). Set the value of your variable accordingly.

Then after the Script task in your control stream, create 2 separate data streams (one for each file connection manager) and use Precedence Constraint set to “connectors” and “Expression and Constraint” to indicate which data stream to use, depending on values ​​of the @IsCrLf variable.

An example of a suggested control flow is below.

Answer 3

how about a derived column with a REPLACE operation after a file source to change CRLF to LF?