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: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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.
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: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
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.
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: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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: Monday, November 24, 2014 12:12 PM
Points: 1,219, Visits: 1,263
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: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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: Monday, November 24, 2014 12:12 PM
Points: 1,219, Visits: 1,263
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