flat file destination question

Question

Post reply

flat file destination question

James Cross-174534

SSChasing Mays

Points: 651
More actions
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

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

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Sugesh Kumar

One Orange Chip

Points: 27311

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

James Cross-174534 SSChasing Mays Points: 651 More actions · Answer 2

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

James Cross-174534 SSChasing Mays Points: 651 More actions · Answer 3

James Cross-174534

SSChasing Mays

Points: 651

July 5, 2007 at 3:42 pm

#717520

Bump!

Any suggestions gratefully received!

Thanks

James

Catherine Eibner Ten Centuries Points: 1344 More actions · Answer 4

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

Martin Schoombee SSCoach Points: 19088 More actions · Answer 5

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.