Knowledge.ToString()

SSIS – Two Ways to Add New Line Character (CRLF) to the Fixed Width Flat File

In SSIS package, if you create a fixed width flat file, SSIS does NOT add new line character (CRLF) at the end of the row.

So when you run the package, all the data are stored in one single line in the flat file.

Add CRLF Column to Destination

You need to add additional column to the fixed width flat file and name it as CRLF (or name of your choice).

This column must have width of 2 characters.

Here are two ways to add new line character after each row

Option 1. New Line Character in Resultset in Source

Add new line character as a column in the SELECT statement. This example assumes source as SQL Server.

SELECT CHAR(13) + CHAR(10)  AS CRLF

Options 2. New Line Character as Derived Column

Create a derived column called CRLF using the following expression.

(DT_STR,2,1252)"\r\n"

Now you have CRLF column in source and destination. Once you map source column to destination column, it will work.

Share

Comments

7 responses to “SSIS – Two Ways to Add New Line Character (CRLF) to the Fixed Width Flat File”

  1. Abhay Avatar
    Abhay

    Thanks a Ton! You Saved my Day! God Bless you Dear!

  2. Alfredo Acevedo Avatar
    Alfredo Acevedo

    Simple and right to the bone!!! It saved my life,too… Thanks

  3. Erik M Avatar
    Erik M

    Thank you very much. Exactly what I needed!
    Simple and to the point

  4. Jorge Avatar
    Jorge

    Thanks, it saved my live 🙂

  5. Aparna Raut Avatar
    Aparna Raut

    Thanks, It solved my problem.

  6. Ben Avatar
    Ben

    Might want to change that CHAR(10) + CHAR(13) to CHAR(13) + CHAR(10) as it’s the wrong way around

    1. Vishal Monpara Avatar
      Vishal Monpara

      Thanks Ben. I fixed it.

Leave a Reply

Your email address will not be published. Required fields are marked *