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

Category: SSIS

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

4 comments

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

  1. Jorge says:

    Thanks, it saved my live 🙂

  2. Aparna Raut says:

    Thanks, It solved my problem.

  3. Ben says:

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