Ok, here's the story and subsequent problem :

I used the DTS Import / Export Wizard (SQL Server 2000 SP3a with Client
Tools patched as well) to generate two VB files: One for exporting a
table to a text file and one for importing the same file back into the
database. Some setting I used are:

- UNICODE output
- CRLF row terminator
- TAB column delimiter
- DOUBLE-QUOTE text terminators
- First Row Has Column Names

In addition to having the wizard generate the VB BAS files, I also had
the wizard execute the import and export at generation time. Both work
fine from the wizard.

I imported the VB code into a new VB project and started by prepping the
Export file. The VB file is not complete (or more accurately,
functional). For example, the TAB column delimiter shows up in VB as a
quoted string with 8 spaces. So, I changed it to use a CHR(9) and tested
and everything exports without a hitch.

oConnection.ConnectionProperties("Column Delimiter") = Chr(9) ' TAB
CHARACTER

I then took the exported text file and used the DTS Import / Export
Wizard to bring it into the database. That worked fine as well. So that
tells me the wizard likes the file just fine.

Lastly, and this is where the problem is, I prepped the Import BAS file
to test the VB Import Routine. Won't execute without throwing an error.
The error every time is on the import step (which is the last step) :

Matching Text Qualifier not found.
Microsoft Data Transformation Services Flat File Rowset Provider
-2147467259

I tried every setting and even regenerated another BAS file to compare
the code. No differences.

If I change the exported/imported file type in the code to ANSI (see
following line), the export and import work without an error and the
file has (as expected) half the number of bytes from the ANSI output.

oConnection.ConnectionProperties("File Type") = 1 ' 2 = UNICODE 1 = ANSI

I thought that possibly one of the problems is that VB was putting
non-UNICODE TAB, CRLF, and DOUBLE-QUOTE characters into the file on the
export using the settings and I tried changing them to, for example,
StrConv(CHR(9), vbUnicode), but that didn't work.

oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
oConnection.ConnectionProperties("Column Delimiter") = Chr(9) ' TAB
CHARACTER
oConnection.ConnectionProperties("Text Qualifier") = """"

The interesting thing is that the exported file from VB compares exactly
to the DTS Exported file (Using WinDiff and TextPad). Not sure if
WinDiff is UNICODE aware.

Any help getting the UNICODE version of the TXT file to import correctly
would be appreciated. I'd be happy to post any code if you need to see
something I haven't addressed.

As I mentioned, this is on SQL Server 2000 SP3a with patched client
tools. I know about the "Max characters per delimited column" issue in
SP1 and prior releases and see the generated code put 8000 characters as
the max. I even changed this to 65535 without a problem (I have an NTEXT
column in the table I'm exporting):

oConnection.ConnectionProperties("Max characters per delimited column")
= 65535

Any help appreciated.

--
David G.