Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Flat File Destination Formatting to add a carriage return Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 9:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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.


  Post Attachments 
NavRoutineResults.txt (10 views, 372 bytes)
Post #1388683
Posted Monday, November 26, 2012 10:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:15 AM
Points: 5,317, Visits: 12,354
In your Flat File connection manager, what is the value of the Row Delimiter property?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1388701
Posted Monday, November 26, 2012 10:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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.


  Post Attachments 
Untitled.jpg (32 views, 81.57 KB)
Post #1388709
Posted Monday, November 26, 2012 10:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:15 AM
Points: 5,317, Visits: 12,354
Have you tried {CR}{LF}?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1388716
Posted Monday, November 26, 2012 10:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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?
Post #1388720
Posted Monday, November 26, 2012 12:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:50 AM
Points: 1,234, Visits: 1,273
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
Post #1388771
Posted Monday, November 26, 2012 12:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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?


  Post Attachments 
Untitled.jpg (13 views, 13.43 KB)
Untitled.jpg (9 views, 13.43 KB)
Post #1388783
Posted Monday, November 26, 2012 12:37 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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.
Post #1388793
Posted Tuesday, November 27, 2012 6:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:50 AM
Points: 1,234, Visits: 1,273
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
Post #1389140
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse