SSIS Text File Destination - Rows not breaking at end of line

Question

SSIS Text File Destination - Rows not breaking at end of line

mweissx5

Ten Centuries

Points: 1147
More actions
October 30, 2006 at 1:32 pm

#113993

Hello,
I am sure I am missing something simple here - I am outputting data from a query in SQL Server 2005 SSIS to a text file destination. The problem I am running into is the rows returned from the query are not being separated into individual rows in the text file - SSIS is concatenating them all into one row. My output file should consist of rows each 133 characters in length in a fixed file format. I have that configured in the properties of the destination connection and have even tried concatenating a cariage return/end of line characters to each row returned by the query with no luck.
Any suggestions would be most appreciated.
Thank you,
Michael

mhweiss

Viewing 15 posts - 1 through 15 (of 17 total)

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

michanne SSCrazy Points: 2329 More actions · Answer 1

Go into the advance tab of your destination (flat file) connection. There is an editor for all of your columns there. You can see what the deliminator is for each column and the last column should be a row deliminator.

Hope that is clear enough and helps.

mweissx5 Ten Centuries Points: 1147 More actions · Answer 2

Thank you for your reply and suggestion. I am using a fixed width output format that consists of one column 133 characters in length. I can not seem to get the destination connection to recognize this as it sets the column width at 1 character and will not let me alter that setting. The data is coming from a simple select statement that selects the one 133 char width field in the output table. Any suggestions would be most welcome!

Thank you,

Michael

mhweiss

michanne SSCrazy Points: 2329 More actions · Answer 3

There are two formats for fixed width - one has a extra column for the deliminated row value. Have you tried that? Unfortunately I do not work with fixed width that often so I am afraid I do not have much more to offer.

ALLAN S KAMARA SSC Enthusiast Points: 184 More actions · Answer 4

We just started using SSIS and have the same problem. Until I can resolve the problem I use a work around of concatenating all the columns in a query making sure to adjust for null values and non char types.

SELECT COL1+COL2+COL3+...

mweissx5 Ten Centuries Points: 1147 More actions · Answer 5

Thank you, I was not aware there were two formats for fixed width. I will try to figure out the configuration for the second format. Regarding the concatenating of the fields, my output table with one field is actually a concatenation of all of the fields making up the row to be output to the fixed width file. Allan, do you append a carriage return character to the end of your concatenated row of fields? I am trying to figure out what you are doing differently that makes the workaround work for you.

Thanks!

Michael

mhweiss

ALLAN S KAMARA SSC Enthusiast Points: 184 More actions · Answer 6

My SQL concatenates all the columns and then I send it to a text file as delimited not as a fixed length. I don't append anything to the end of the record.

Allan

mweissx5 Ten Centuries Points: 1147 More actions · Answer 7

Perfect! Thank you. That works fine...but wouldn't it be nice to know how it is supposed to work?! I appreciate everyone's input and helpful suggestions.

Thanks,

Michael

mhweiss

Rob LeDrew SSC Enthusiast Points: 142 More actions · Answer 8

I am having this exact same issue. I would love to know if anyone figured out how to do it or if it was a bug.

Rob LeDrew SSC Enthusiast Points: 142 More actions · Answer 9

I changed it to ragged right format and added a last column that was {cr}{lf} and that fixed it. I had fixed width as the format before when it wouldnt work. Problem Solved!

bdaniel-292473 Valued Member Points: 54 More actions · Answer 10

Please read this article http://www.coderewind.com/?categ&view_article=105 which answers the above issue.

Binoj
CodeRewind.com

mweissx5 Ten Centuries Points: 1147 More actions · Answer 11

Thank you. I found the same answer through some research and trial and error. Hopefully this will be resolved in a future version of SSIS.

Thanks,

Michael

mhweiss

andy-787649 Grasshopper Points: 17 More actions · Answer 12

I'm having the same problem, did anybody find the solution for using just plain fixed width, as opposed to ragged right (which works perfectly, so I'm using this for now)?

mweissx5 Ten Centuries Points: 1147 More actions · Answer 13

I think the problem with the fixed width format will have to be addressed by the Sql Server SSIS team. I just use the ragged right format since it works without any problems.

Regards,

Michael

mhweiss

bdaniel-292473 Valued Member Points: 54 More actions · Answer 14

yes, I agree, I think we might have to wait till they fix this. May be in the next SP.

Binoj
CodeRewind.com