SQL 2000 DTS export ntext to flat files with unicode data

[ckdong]

Hello.

I have unicode data in a ntext field.
I am attempting to use DTS to export the data to flat files.

The table stores data in various languages. I would like to export each
record to its own file for archival/data-retention purposes.

A file will be created for each record in my source table.

The file names are based on data in a nvarchar column. I am able create
files with Japanese or Chinese characters.

Unfortunately, the file contents have question marks in place of Japanese or
Chinese characters.


How can I export the ntext data to flat files and preserve the language?
ie. not get question marks?


When I run a query in Query Analyzer from the actual server (Win 2000
Server), the data appears as blocks.
When I run the same query from my desktop computer (Win XP), the data
appears as it should-- Japanese or Chinese, etc characters. I have no problem
copying and pasting to Excel.

DTS package description
- dynamic properties task to set global variables, including file path for
file creation
- activex script task to delete all files in file path
- microsoft ole db object (database connection properties)
- transformation data task
- use query to return a file name and the data
- build file name using nvarchar column data
- data is ntext column data
- write file transformation
- text file (destination)


Alternatively, I can use Query Analyzer and attempt to copy-and-paste the
data to Excel. However, there are over 70,000 records and I'm sure many of
the messages are extremely large. At some point, Excel probably won't be able
to take it.


Microsoft SQL Server 2000 - 8.00.2148 (Intel X86)
Standard Edition on Windows 2000 (Build 2195: Service Pack 4)

Thank you very much.
I appreciate any feedback and/or advice.

Sincerely,
ckdong

[ckdong]

I found an error in the DTS package.

In the actual Transformation task, I had neglected to modify the properties
of the write_file transformation. I changed the type from 'ansi' to
'unicode'.

Thanks to everyone that took the time to read and consider my post.

ckdong

---------------------

Post by ckdong
Hello.
I have unicode data in a ntext field.
I am attempting to use DTS to export the data to flat files.
The table stores data in various languages. I would like to export each
record to its own file for archival/data-retention purposes.
A file will be created for each record in my source table.
The file names are based on data in a nvarchar column. I am able create
files with Japanese or Chinese characters.
Unfortunately, the file contents have question marks in place of Japanese or
Chinese characters.
How can I export the ntext data to flat files and preserve the language?
ie. not get question marks?
When I run a query in Query Analyzer from the actual server (Win 2000
Server), the data appears as blocks.
When I run the same query from my desktop computer (Win XP), the data
appears as it should-- Japanese or Chinese, etc characters. I have no problem
copying and pasting to Excel.
DTS package description
- dynamic properties task to set global variables, including file path for
file creation
- activex script task to delete all files in file path
- microsoft ole db object (database connection properties)
- transformation data task
- use query to return a file name and the data
- build file name using nvarchar column data
- data is ntext column data
- write file transformation
- text file (destination)
Alternatively, I can use Query Analyzer and attempt to copy-and-paste the
data to Excel. However, there are over 70,000 records and I'm sure many of
the messages are extremely large. At some point, Excel probably won't be able
to take it.
Microsoft SQL Server 2000 - 8.00.2148 (Intel X86)
Standard Edition on Windows 2000 (Build 2195: Service Pack 4)
Thank you very much.
I appreciate any feedback and/or advice.
Sincerely,
ckdong