What flavor Unicode is used for flat file export?

[devRyan]

Ok, here's one more that I can't figure out. What flavor Unicode is used for flat file export? I would assume UTF-8, but I can't prove it.

Here's my problem. I'm using DTS to export some table data into a plain text(.txt) file. I then upload this file on a clients web portal for use in one of their systems. After the batch is run on their end, I'm sent back an error list.

Most recently, I've been getting back errors(only on about 1/3 of the rows) stating that the data must be in cp1252 or utf-8.

Now this confuses me somewhat, since one is unicode, and the other is not. But aside from that for now....

I've been sending the files in as ANSI, I'm in the US, so ANSI is most commonly related to 1252, basically. So some of the rows succeed while others fail, with the message about send data as cp1252 or utf-8.

So I've been looking for ways to have DTS export my files directly to UTF-8. In my searching, I find the handy destination properties for the Text File (Destination) Connection in DTS Designer. I switch my choice from ANSI to Unicode. I rerun my file, as UNICODE. Try to upload it on the clients portal, and I get a failure message stating that my file rejected because it was a binary file.

Huh?

So now I'm back to exporting to ANSI, then using Notepad to convert the file to utf-8 manually, which is not what I want, since one of the points on the exercise is to automate the file delivery.

So does DTS export to some other form of Unicode? I's there somewhere that the Unicode format can be specified?

My other file type choice is csv. Would I be better off going with a .csv file? What would be the pros and cons?

I appreciate any clarification provided.

devRyan

 

[fuzzyocelot]

I'm guessing that you're probably using SQL Server 2000. Is that right? I thought that SQL Server 2000 used UTF-8 but from what I just read, it sounds like it doesn't. Here's the KB article I found on Microsoft's website.

http://support.microsoft.com/kb/232580

Have you tried looking at the data to see if anything looks strange? Generally when I get those kinds of errors, it means that something isn't right in my file. You can try a csv file just to see if you get the same result.

 

[fuzzyocelot]

I also found an article that says "Although SQL Server 2000 does not store data in UTF-8 format, it supports UTF-8 in at least one crucial scenario: its support of the Extensible Markup Language (XML). "

http://msdn2.microsoft.com/en-us/library/aa902644(sql.80).aspx

Just more food for thought.

 

[devRyan]

Thanks for the help, fuzzy.

I'm still dealing with this issue, though I've found a method of working around it, and probably a more proper way of dealing with the problem, in general.

I'm now working on translating the entire file into UTF-8 using a script, while still inside the DTS package.

For reference sake:

http://www.tek-tips.com/viewthread.cfm?qid=1392917