SSIS prints Unicode strings to a text file as shift-JIS character codes

Page updated :

Patterns that result in errors

Assume that the nvarchar column of a table on SQL Server has a string that cannot be used by Shift-JIS. By the way, if you put these characters in the varchar column, the characters that cannot be used like the ColumnShiftJis column on the right are not available? will be replaced.

Unicode 文字をデータベースのテーブルに入れる

On SSIS, load the data from the previous table and assemble the flow to output to a text file.

テキストファイルへ出力する様にフローを構築

Output as a CSV file. Because Shift-JIS is often used in CSV files, the character code of the text file is set to Shift-JIS.

文字コードを Shift-JIS に設定

If you run this setting, you will get an error at the timing of the output.

テキストファイル書き込み時にエラー

You receive an error message similar to the following:

エラー内容

The output file is also cut off halfway through the time the error occurs.

出力されたテキスト

If you try to output it as it is, it will result in an error, so you will have to incorporate "data conversion" in the middle of the trial.

データ変換 追加

Convert the data type from "DT_WSTR" to "DT_STR" and make it possible to output to Shift-JIS.

「DT_WSTR」から「DT_STR」に変換

However, if you do this, you will get an error. In SSIS, implicit processing that causes data to be missing is strictly an error. If you want to do something that causes text to fall out on SSIS, you will have to explicitly specify character substitution for all characters.

データ変換エラー

What to do

You can use SQL to change the type of text when you load from a table in a database. Sql runs the SQL Server engine, not SSIS, so you can run without errors. By the way, all characters that cannot be used in Shift-JIS are "?" will be converted to. This is a SQL Server specification. The same results are true for all systems that use SQL Server, even if it is not SSIS.

Open the ADO NET source and change the data access mode to SQL Commands. Now that you can enter SQL, you write a Select statement that retrieves data from the database. The text that contains characters that are not available in Shift-JIS at that time is forced to convert to varchar using cast.

SQL で varchar に変換

When data flows through SSIS, characters that are already unavailable in Shift-JIS have been removed, so you can continue to process them.

処理成功

You can see that the text has been written to csv successfully. In addition, all characters that cannot be used in Shift-JIS are you can see that it has been converted to.

出力されたテキスト