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

  • 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

  • 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.

  • 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

  • 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.

  • 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+...

     

     

     

  • 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

  • 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

  • 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

  • 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.

  • 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!

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

    Binoj
    CodeRewind.com

  • 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

  • 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)?

  • 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

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

    Binoj
    CodeRewind.com

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

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