October 8, 2010 at 12:10 am
What can I do to prevent the last record in a delimited flat file from having a trailing delimiter pair (CR/LF)? Or is there a way to drop the two bytes before sending the file along?
I thought I had an easy assignment here. Use a "Select distinct" query as the source in my MS SQL db for a flat file that simply lists unique account numbers. The receiving application, however, is trying to load that list to a Netezza database (I think it's on a Unix box, but don't know), and their folks tell me that since the file ends with a CR/LF pair, their load routine thinks there should be another record. Using Notepad++ (a fine free editor, BTW), the last few records look like the first screen shot here, but I want the file to end without the CRLF as in the second image.
So, is there a way to control this behavior in SSIS? or how can i correct it?
October 8, 2010 at 8:03 am
Maybe you could try a script task to solve this problem. After you've created the file, you could open it in .NET and go to the last line to delete the row delimiter (if possible).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 8, 2010 at 8:31 am
da-zero (10/8/2010)
Maybe you could try a script task to solve this problem. After you've created the file, you could open it in .NET and go to the last line to delete the row delimiter (if possible).
Sure it's possible - I was thinking the same thing.
As long as the file isn't too big, should work fine. Might need to use something like a ByteStream to get at the unreadable characters.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 8, 2010 at 8:55 am
Phil Parkin (10/8/2010)
da-zero (10/8/2010)
Maybe you could try a script task to solve this problem. After you've created the file, you could open it in .NET and go to the last line to delete the row delimiter (if possible).Sure it's possible - I was thinking the same thing.
Great. It's been a while since I've done something in .NET, so I wasn't too sure.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 8, 2010 at 2:47 pm
There might be a simpler solution that you miight want to try before doing the .NET stuff. Try either just CR or just LF as the row delimiter, if you can.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 9, 2010 at 3:22 am
Alvin Ramard (10/8/2010)
There might be a simpler solution that you miight want to try before doing the .NET stuff. Try either just CR or just LF as the row delimiter, if you can.
That is actually a very good idea, as Windows operating systems use both CR and LF as row delimiters, but Unix based systems only one of both (I'm not sure which one).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2010 at 8:12 am
da-zero (10/9/2010)
Alvin Ramard (10/8/2010)
There might be a simpler solution that you miight want to try before doing the .NET stuff. Try either just CR or just LF as the row delimiter, if you can.That is actually a very good idea, as Windows operating systems use both CR and LF as row delimiters, but Unix based systems only one of both (I'm not sure which one).
That's what I was thinking.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 12, 2010 at 3:32 pm
Using just LF as a record delimiter did the trick. A quick internet search confirmed that LF was a standard on UNIX, and this is certainly simpler than doing some sort of post-creation file manipulation.
Thank you to all who helped here.
October 12, 2010 at 3:50 pm
john.arnott (10/12/2010)
Using just LF as a record delimiter did the trick. A quick internet search confirmed that LF was a standard on UNIX, and this is certainly simpler than doing some sort of post-creation file manipulation.Thank you to all who helped here.
WOOHOO!!! That's good to know.
You're welcome.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply