SSIS: Source has UNICODE data; I'm using MySQL 64bit ANSI ODBC driver

Question

Source has Unicode data but I am using MySQL 64 bit ODBC ANSI driver which is throwing an error after partial load (Errors out after it comes across few special characters) .

Is there any work around for this (other than switching to Unicode Driver)?

Answer 1

This worked: CONVERT(columnName USING ASCII) as columnName

Answer 2

SSIS is very stringent with meta data requirements. You have two choices: switch drivers or re-encode the source data to ANSI.

There is a third choice: extract the UNICODE data to a CSV file and then load the file via a Flat File Connection. From there, you can instruct the connection manager to use ANSI or UNICODE.

I implement a more scalable method via flat file imports by allowing for conversion of any encoding that is support by .NET via a PowerShell script (digitally signed of course). In this case, the data provider can throw anything at me and I can encode it to ANSI before loading it.

The solution really depends on what the current and potential future data requirements are. I hope this help.

Update:

I am not a MySQL expert as I do not dev on it. If you are using native conversion functions within the server, you might want to understand fully how it handles conversion of varchar and nvarchar strings when going from UNICODE to ANSI. Again, I turn over the re-encoding to .NET as opposed to server functions.

https://stackoverflow.com/questions/32367311/convert-to-ascii-char-in-sql-server
https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html
https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html