SSIS outputs a Unicode string as a Shift-JIS character code to a text file

Page creation date :

Patterns that result in errors

Assume that a column of type nvarchar in a table on SQL Server has a string that cannot be used in Shift-JIS. By the way, if you put these characters in the varchar column, there are characters that can not be used, such as the "ColumnShiftJis" column on the right. Replace it.

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

On SSIS, the flow is set up to read data from the previous table as shown in the figure and output it to a text file.

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

Assume that you want to output it as a CSV file. Since Shift-JIS is often used in CSV files, set the character encoding of the text file to Shift-JIS.

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

If you execute with this setting, an error will occur at the timing of output.

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

An error message similar to the following appears:

エラー内容

The output file will also be interrupted halfway through when the error occurred.

出力されたテキスト

If you try to output as it is, it will be an error, so try incorporating "data conversion" in the middle.

データ変換 追加

Convert the data type from "DT_WSTR" to "DT_STR" and make it into a form that can be output to Shift-JIS.

「DT_WSTR」から「DT_STR」に変換

However, if you also execute this, you will get an error. This is because SSIS strictly fails implicit processing that causes data to be missing. If you want SSIS to perform text detyped processing, you will need to explicitly specify character substitution for all characters.

データ変換エラー

Workarounds

There is a way to change the type of text using SQL when reading from a table in a database. If it's SQL, it's the SQL Server engine that runs it, not SSIS, so it can run without errors. By the way, all characters that can not be used in Shift-JIS are "?" Converted to: This is a SQL Server side specification. Even if it is not SSIS, the result is similar for all systems that use SQL Server.

Open ADO NET Translator and change the data access mode to SQL Command. Now that you can enter SQL, write a Select statement to retrieve data from the database. Text that contains characters that cannot be used in Shift-JIS is forcibly converted to varchar using cast.

SQL で varchar に変換

Since characters that cannot be used in Shift-JIS have already been removed at the time of data flowing to SSIS, processing can be passed as it is.

処理成功

You can see that the text is successfully written to the CSV. Also, all characters that cannot be used in Shift-JIS are "?" You can see that it has been converted to:

出力されたテキスト