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

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.

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.

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.

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.

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.