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


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.

  • 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 '13 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 '13 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 '13 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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