How can I get rid of the the last carriage return/last line of a file using flat file destination?

Question

Post reply

How can I get rid of the the last carriage return/last line of a file using flat file destination?

rwc

SSC Enthusiast

Points: 140
More actions
June 11, 2009 at 6:47 pm

#205942

I generate a comma separated file with {cr}{lf} for the rows. At the end of the last row it adds another line. How can I remove the last {cr}{lf}?
Thanks
Bob

Viewing 4 posts - 1 through 4 (of 4 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

declare @CRLF = char(13) + char(10)

set @YourString = replace(@YourString, @CRLF + @CRLF, @CRLF)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

rwc SSC Enthusiast Points: 140 More actions · Answer 2

I appreciate your response. However I'm not sure I understand or maybe I'm not being clear. It is the text file that I'm generating that has the extra line at the end I want to remove. The carriage returns/line feeds are created in the flat file connection manager by design. It just adds one to many.

Thanks

Bob

WayneS SSC Guru Points: 95461 More actions · Answer 3

Whoops, I didn't notice that you were in the SSIS forum - I gave you a T-SQL solution.

I'll have to let someone that knows SSIS better answer this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2