MySQL to SQL Server migration problem with connection string

Question

I am trying to import data from MySQL to SQL Server using the SQL Server Management Studio > Task > Import Data Wizard.  The goal is to import data into SQL Server (running on a 64 bit platform) from an ODBC connection to a MySQL instance (running on a 32 bit platform.)  

Following guidelines published in a white paper, I chose the option ".NET Framework Data Provider for ODBC."

Next I specified the DSN using the ConnectionString:

Dsn=<DSNNAME>;Driver={MYSQL ODBC 5.1 Driver};server=<SERVERNAME>;uid=<USERNAME>;pwd=<PWD>

I received the following error:

"The specified DSN contains an architecture mismatch between the Driver and the Application"

According to the following technote:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362(v=vs.85).aspx

... the problem might be caused by the fact that our SQL Server machine is 64 bit, and our MySQL machine is 32 bit. Here is the recommended fix:

"To resolve this error, use the 32-bit odbcad32.exe to configure or remove the DSN."

Can someone help / advise me please?  How do I "use the 32 bit odbcad32.exe"?

I have tried to uninstall / reinstall my MySQL ODBC connector both 32 bit and 64 bit versions. 

I am not finding any solution so far to this problem.

The work around is I have to use messy MS Access to stage the data and import it from there. I would prefer not to do that.

Thanks,

Kent Lewandowski
Senior Data Consultant
Kaiser Permanente

Answer 1

Hello,

Please try using SSMA for migrating data from MySQL to SQL Server.

http://blogs.msdn.com/b/ssma/archive/2011/02/07/mysql-to-sql-server-migration-how-to-use-ssma.aspx

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Kent -

Please remove all versions of the MySQL ODBC from your/the machine you are working with.

Install JUST the 32 bit version of the ODBC connector (http://dev.mysql.com/downloads/mirror.php?id=407791#mirrors)

Once installed open C:\Windows\SysWOW64\odbcad32.exe

Configure the datasource using this executable.

When setting up your source or destination connector using SQL Server just use:  DSN=<your DSN name>

You should be good to go

Dave

Answer 3

Hi,

I have to migrate from Mysql to SQL server 2008 r2. I chosed SSMA for mysql5. OS is windows server 2008 r2 enterprise, 64bit.

I installed mysql-connector-odbc-5.2.2-winx64.msi in target machine(where sql server is installed) before installing 'SSMA for MySQL 5.2.exe'. But when I am tring to install 'SSMA for MySQL 5.2.exe' it give error saying 'Set up is unable to find Mysql odbc driver v5.1 and above'. If any one have solution for it or had faced simmilar issue please help.

Is MySQL Client Provider means Mysql odbc driver v5.1(mysql-connector-odbc-5.2.2-winx64.msi ). Because ssma extension pack for mysql required pre installtion of MySQL Client Provider.

Your help is appreciated. Reply me on [e-mail]

Thanks,

Umakanta