CODEWITHSUNDEEP
sqlssis
Dayton Brown
Dayton Brown

Reputation: 1250

Reading a text file with SSIS with CRLF or LF

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.

Upvotes: 8

Views: 11259

Answers (3)

sorrell
sorrell

Reputation: 1871

I second the OP's vote for Swiss File Knife.

To integrate that, I had to add an Execute Process Task: exeproc

However, I have a bunch of packages that run For-Each-File loops, so I needed some BIML - maybe this'll 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>

Upvotes: 0

GShenanigan
GShenanigan

Reputation: 5493

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.

Example SSIS Control Flow

Upvotes: 7

Diego
Diego

Reputation: 36176

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

Upvotes: 1

Related Questions