Post reply

flat file destination question

  • July 3, 2007 at 7:40 pm

    #117596

    Hi

    I am running a ssis job which uses a dataflow task to move data to a csv file. Currently the last row in the destination flat file has the row delimiter on the end of it, which causes the file to have a carriage return at the end of it.

    How could I remove this last carriage return using SSIS?

    Thanks in advance

    James

  • July 3, 2007 at 10:47 pm

    #717051

    It can be removed by specifying the properties in the transformation task. The you can specify the row/column delimiters and the behaviour.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 4, 2007 at 5:21 am

    #717125

    Hi

    Thanks for the response.

    I have specified comma as the field delimiter, no header row and <cr><lf> as the row delimiter.

    The problem I have is this. Say the source supplies two rows; each row is written to the csv file with a <cr><lf> row delimiter at the end. This means that the file actually looks like this (the square brackets mark the beginning and end of the file just for this posting so you see what I`m talking about).

    [aa,111,f

    bbb,222,g

    ]

    I want the file to look like this:

    [aa,111,f

    bbb,222,g]

    How do I suppress the row delimiter (or at least the carriage return) for the very last row?

    Thanks again

    James

  • July 5, 2007 at 3:42 pm

    #717520

    Bump!

    Any suggestions gratefully received!

    Thanks

    James

  • July 5, 2007 at 11:55 pm

    #717566

    I had a problem with this once where the column seperator was also adding a carriage return to the output. It was hard to find as it was in the columns definition rather than on the destination file main properties page - but once I removed that all worked well!

    Thanks,

    Catherine

    Kindest Regards,

    Catherine Eibner
    cybner.com.au

  • July 6, 2007 at 12:46 am

    #717586

    You will probably have to write a vb/java script that:

    • opens the file after you have populated it
    • moves to the end of the file and check whether it is a carriage return (char(13) I think)
    • removes the carriage return
    • save the file again

    Hopefully the files aren't too big, otherwise this could be a rather time-consuming exercise. I don't know of any properties you can set to get rid of this, so this might be the only way to do it.

  • July 9, 2007 at 6:24 pm

    #718142

    Thanks to everyone who has replied.

    Catherine, I`ll check the column definition out, though I`m pretty sure its set to comma as the output is a csv file.

    Martin, I feared that was the answer, if all else fails I`ll get it done this way

    Thanks again

    James

  • July 9, 2007 at 7:07 pm

    #718147

    Hi James,

    I decided to post my resolution to this problem on my blog. Check it out - it may help

    http://blog.cybner.com.au/2007/07/ssis-extra-carriage-return-at-end-of.html

    Thanks,

    Catherine

    Kindest Regards,

    Catherine Eibner
    cybner.com.au

Viewing 8 posts - 1 through 7 (of 7 total)

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