SSIS – Two ways to add new line character (CRLF) to the Fixed Width flat file

By | January 26, 2017

In SSIS, for fixed width flat file, new line character (CRLF) is NOT added at the end of the row. So when you run the package, all the rows are stored in one single line in the flat file.

Prerequisite

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

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

1. New line character in resultset

Add new line character as a column in the SELECT statement.

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

Now you can use this column to map to an artificial CRLF column in the fixed width you created

2. New line character as derived column

Create a derived column called CRLF using the following expression and map it to the artificial CRLF column in the fixed width you created

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

And your problem is fixed.

Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

Leave a Reply

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