I don't know if this has been documented anywhere, but it's news to me.
We were having problems attempting to import a Unicode, tab-delimited,
flat-file into SqlServer 2000 using a DTS package.

One of the things we found is that DTS will only import a Unicode
(UTF-16) file if the field-data is surrounded by a text-qualifier. It
doesn't care what you use as a field-separator; it just needs that
text-qualifier. Use CSV and avoid headaches.

The next, more important thing: the file MUST be in Little-endian
(0xFFFE). DTS cannot handle Big-endian (0xFEFF) in SqlServer 2000. We
tried 2 files, where the only difference was that one was Little-endian
and the other was Big-endian, the Little-endian version imported while
the Big-endian version failed; yes, they both had the correct
byte-order-marker (BOM). (And VS.Net 2003 displayed both files correctly.)

Granger
PS: If your double-quote character looks like this in hex "0x0022",
that's Big-endian; you want "0x2200".

PPS: Does anyone know if DTS uses the Microsoft.Jet.OLEDB.4.0 provider
underneath when parsing text files? That would explain a problem I had
on an earlier experiment I tried.

Granger,

Have you considered old fashionate BCP?

Ilya

"Granger Godbold" <granger.godbold (AT) FILLER (DOT) prometric.com> wrote