How to export Japanese characters from SQL Server 2008 database to a text file?

0
votes

I have an odd problem. I need to export Japanese characters from a table to a raw text file. When I run the select statement in SQL, I am able to see the characters displayed correctly. However, when I run the SSIS package to export these values to a text file they are displayed as ?'s.

Data type of the field is NTEXT. Has anyone ran into this problem before?

SQL statement: 

select cast(body as nvarchar(max)) as body from msgsMarket

In SSIS package's flat file connection manager, I have set the output encoding to use 932

1
sqlsql-server-2008ssis
What encoding are you writing out the files as? – Oded
Asp.net Forum Those functions should still be valid and as @Oded stated its all about the encoding. – Pedro Ferreira
@Oded Please see edit, i am able to extract them successfully when i run the query as 'save results to text', but i need to run this as an automated process – some_bloody_fool
What happens if you use UTF-8 or UTF-16? – Oded
@Oded If i do that in ssis i get close, the result is something like this: x003C_none_x003E_あいよ_x003C_none_x003E – some_bloody_fool

1 Answers

3
votes

This is not a solution but might probably help you to identify the problem in your case.

Created a sample SSIS package using SSIS 2008 R2 with UTF-8 and Unicode encodings and the SQL Server data exported correctly to flat files.

Sample SQL data in the file. Description field was of data type NVARCHAR. The sample was also tried by changing the data type of the Description field to NTEXT and the flat files still exported correctly.

Sample SQL data

SSIS package was created with a data flow task with two outputs for UTF-8 and Unicode.

Data flow task

First flat file connection manager to generate flat file with encoding UTF-8.

UTF-8 encoding

Output file generated with UTF-8 encoding.

UTF-8 output file

Second flat file connection manager to generate flat file with encoding Unicode.

Unicode encoding

Output file generated with Unicode encoding.

Unicode output file