SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Flat File Destination Formatting to add a carriage return


SSIS Flat File Destination Formatting to add a carriage return

Author
Message
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 479
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.
Attachments
NavRoutineResults.txt (66 views, 372 bytes)
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19019 Visits: 20461
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 479
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.
Attachments
Untitled.jpg (149 views, 81.00 KB)
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19019 Visits: 20461
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 479
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
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 1493
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
kwoznica
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 479
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?
Attachments
Untitled.jpg (67 views, 13.00 KB)
Untitled.jpg (50 views, 13.00 KB)
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 479
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
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 1493
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search