How to solve loading Chinese character that turns into "?" (question mark) in SQL Server 2012 using SSIS (Data Source: MySQL)
I have been hell-bent searching for answers about utf8 support for MS SQL Server, I was able to make it work with series of trial and error for collation and codepage settings, hope this also works for you!
* Assuming that you installed the appropriate MySQL driver, specified the required source connection details, and created your linkedserver in MS SQL Server already
* I’ve
included some screenshots for proper settings in Connection, and
Cursors/Results tabs
2. In your destination database, MS SQL Server Management Studio (SSMS), set the database Collation to “Chinese_PRC_CI_AS”, and be sure to set the column data type to nvarchar (for fields with Chinese characters)
3. At this point, when you query directly from MySQL database with Chinese characters using MS SQL linkedserver, expect that you will see weird characters (for instance: æ±äº¬), it’s ok for now, but once it’s been loaded to MS SQL, you should be able to retrieve the correct Chinese characters.
4. In your SSIS package, drag your DFT and in your OLE DB Source task>Show Advanced Editor>Component Properties tab:
a. Set AlwaysUseDefaultCodePage to True
b. Set DefaultCodePage to 65001 (code for UTF8 support in MS SQL) – also check Input and Output Properties tab and set 65001 CodePage, accordingly.
5. Add Data Conversion task and change the column data type to Unicode and set appropriate length.
6. In your OLE DB Destination task>Show Advanced Editor>Component Properties tab:
a. Set AlwaysUseDefaultCodePage to True
b. Set DefaultCodePage to 65001, also check Input and Output Properties tab and set 65001 CodePage, accordingly.
Run
your package and you should be able to see the Chinese characters when you query from your MS SQL table by now, just as how you see it from the source, good luck! Hope this helps!
Comments