Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I am using ssis to generate some .dat files which are then sent to linux server for automated load. It looks as windows add some symbol character in file which is not easily identifiable first but if i open file on linux server i can see that symbol.

For eg. on our side file looks fine but on linux side when opened I see ÿþ is added as first charater in file. This is UTF-16 (LE) little-endian character represented as Char(255) and Char(254) in decimal. I tried to use replace to remove this symbol but didn't work For eg. Select replace(replace('ÿþ01CAP 00820130208',char(255),''),char(254),'')

This works fine for string but there is nothing to replace in the file generated on windows side. Once it is uploaded to linux that symbol is sticked into it.. Any ideas ? What could be wrong here ? I tried to use diff collation in query that generates this file but no luck

Thanks, NJ

share|improve this question
Unix workaround: dd bs=2 skip=1 if=input.txt of=output.txt. But, your file is Unicode. en.wikipedia.org/wiki/Byte_order_mark is what you are seeing at the start of your file – Phil Feb 14 at 19:10
Do you unix2dos the file? – Mike Fal Feb 14 at 19:11
1  
See @RemusRusanu's post about Unicode BOM headers. That was what came to mind immediately when I saw the question. – ConcernedOfTunbridgeWells Feb 14 at 19:27
See this article for a rundown on unicode-ascii conversion on Linux – ConcernedOfTunbridgeWells Feb 14 at 19:32
Thanks for the article. Good info in there. – njvds Feb 14 at 19:34

1 Answer

up vote 2 down vote accepted

Unicode files should have BOM, is the recommended and accepted way, specially for LE:

In UTF-16, a BOM (U+FEFF) may be placed as the first character of a file or character stream to indicate the endianness (byte order) of all the 16-bit code units of the file or stream. If the 16-bit units are represented in big-endian byte order, this BOM character will appear in the sequence of bytes as 0xFE followed by 0xFF. This sequence appears as the ISO-8859-1 characters þÿ in a text display that expects the text to be ISO-8859-1.
...
"The UTF-16 encoding scheme may or may not begin with a BOM. However, when there is no BOM, and in the absence of a higher-level protocol, the byte order of the UTF-16 encoding scheme is big-endian."

'linux' is a very generic term. You must be using some application for processing, and if the application is not recognizing the BOM it means is a bad application. Ditch it for something better.

share|improve this answer
I was going to post this as an answer - +1. Note that SSIS as the output medium is likely not easily amenable to replacement if it's doing more than dumping the output. – ConcernedOfTunbridgeWells Feb 14 at 19:30
Yes. There is application that parses file and load it in DB on linux side. But even if i open file directly on my RedHat CentOS vm I can see that character. Interesting thing to notice here is if I generate simple text file no my machine and copy it to my vm then I don't see this symbol. Only Sql Server SSIS generated files seems to have that symbol. Is it possible that WinSCP that I am using here to upload files changing format or something ? Linux team is looking into their application too at the moment. – njvds Feb 14 at 19:32
1  
.Net streams add the BOM by default, unless explcitly told not to, see msdn.microsoft.com/en-us/library/984ebfcy.aspx Well written apps will look for the BOM, read it and interpret it but don't display it, so you won't see it even when is there. See stackoverflow.com/questions/1068650/… for a number of linux one-liners to remove the BOM – Remus Rusanu Feb 14 at 19:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.