SSIS Flat File Destination Formatting to add a carriage return

Question

SSIS Flat File Destination Formatting to add a carriage return

kwoznica

SSCertifiable

Points: 5275
More actions
November 26, 2012 at 9:33 am

#285436

I have an SSIS package I am building. The result is a flat file created from a sql query that is emailed via the send mail task. The package runs and I receive the email however the flat file attachment containing the data is all on one line in the text file.
How can I make ssis create a carriage return for each record instead of everything appearing on one line?
I have tried changing settings in the flat file connection manager editor but have come to no avail.
Attached is a sample of the output flat file. Thanks for your suggestions.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

In your Flat File connection manager, what is the value of the Row Delimiter property?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kwoznica SSCertifiable Points: 5275 More actions · Answer 2

Phil,

I have the format set at fixed width and the row delimeter is {cr}. I also attached a print screen.

thanks for taking a look.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Have you tried {CR}{LF}?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

kwoznica SSCertifiable Points: 5275 More actions · Answer 4

I have as that was the default. I changed a few different options but they did not take effect.

what other way can you create a carriage return?

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 5

kwoznica (11/26/2012)
I have as that was the default. I changed a few different options but they did not take effect. what other way can you create a carriage return?

I would guess that you only have a LF (ASCII Char(10)) as opposed to CR LF (ASCII Char(10) + ASCII Char(13)). But rather than going on my guess, open the source file in an editor that can display the end of line characters (Vim or NotePad++ as well as month others editors can).

Take a look and see if that helps,

Rob

kwoznica SSCertifiable Points: 5275 More actions · Answer 6

Robert,

The source is an OLD DB connection that is running the below sql statement

select [Entry No_]

, ID

, [User ID]

, [Start Date_Time]

, [End Date_Time]

, Status

, ';' AS Delimeter

from dbo.[Live$Job Queue Log Entry]

where [Start Date_Time] > (GETDATE()-1);

Also see the attached data flow objects.

I tried adding a column as the delimeter hoping ssis would see it and recognize it but the output file is still on one line.

Would it be helpful if I used vim or notepad++ to look at the results?

kwoznica SSCertifiable Points: 5275 More actions · Answer 7

Ok well I was able to get it to work.

In Flat File Connection Manager Editor, Under General, I set the Format to Ragged Right and Header Row Delimeter to Semicolon;

Now the output file looks tidy. Thanks for everyone's help.

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 8

kwoznica (11/26/2012)
Ok well I was able to get it to work.
In Flat File Connection Manager Editor, Under General, I set the Format to Ragged Right and Header Row Delimeter to Semicolon;
Now the output file looks tidy. Thanks for everyone's help.

I'm glad you got it working,

Rob