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

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.

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.

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.

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.

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: