Connection to mySQL fails (architecture mismatch)

Question

Connection to mySQL fails (architecture mismatch)

SpeedSkaterFan

SSCrazy

Points: 2716
More actions
September 1, 2014 at 12:06 pm

#290297

Hi all,
I would like to use SSIS to get data from a mySQL database to SQL Server.
I have created a project incl package. In this package I have added an ADO net source and ADO net destination, linked together
If I try to connection to the mySQL source I get the error message:
"
Test connection failed because of an error in initializing provider ERROR [M014] [MICROSOFT][ODBC Driver manager]
The specified DSN contains an architecture mismatch between driver and application
"
I have searched this forum (and more) to find out what the problem might be and I think the problem is that my system is 64-bit, and the mySQL programm is 32-bit?
In SQL Server I have created a linked server and this works fine (rather quickly actually).
I use the same ODBC 5.2 driver for both connections.
What can I do to connect SSIS to mySQL?
Thanks in advance
Hein

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 1

SpeedSkaterFan (9/1/2014)
Hi all,
I would like to use SSIS to get data from a mySQL database to SQL Server.
I have created a project incl package. In this package I have added an ADO net source and ADO net destination, linked together
If I try to connection to the mySQL source I get the error message:
"
Test connection failed because of an error in initializing provider ERROR [M014] [MICROSOFT][ODBC Driver manager]
The specified DSN contains an architecture mismatch between driver and application
"
I have searched this forum (and more) to find out what the problem might be and I think the problem is that my system is 64-bit, and the mySQL programm is 32-bit?
In SQL Server I have created a linked server and this works fine (rather quickly actually).
I use the same ODBC 5.2 driver for both connections.
What can I do to connect SSIS to mySQL?
Thanks in advance
Hein

Quick solution would be to query the MySql via the linked server connection.

😎

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 2

Hallo Eirikur,

I don't think that is the solution

The linked server only allows me a small portion of the things I want to do

1 of the tables I need to address is over 10 million records, which is far to large for a linked server (as I found out)

Hein

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 3

SpeedSkaterFan (9/1/2014)
Hallo Eirikur,
I don't think that is the solution
The linked server only allows me a small portion of the things I want to do
1 of the tables I need to address is over 10 million records, which is far to large for a linked server (as I found out)
Hein

Got it;-)

Have you tried MariaDB ODBC[/url] drivers instead, I remember that solving some issues?

😎

Edit: added link

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 4

Hallo Eirikur,

I will give it a go, thanks for your suggestion

Hein

shahnawaz052 SSC Enthusiast Points: 163 More actions · Answer 5

Download ODBC 5.1 driver 32 bit. I had also same problem while creating data source for SSRS report. It's resolve after installing ODBC 5.1 32 bit driver.

🙂

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 6

Hi Shahnawaz,

I did as you suggested.

After installing the driver it took a while to get it mentioned in the administrative tools\data sources (odbc) dialogbox, but using the file C:\Windows\SysWOW64\odbcad32.exe got that solved (for other readers, this is where I found the information: http://www.milanm.com/?p=556

Now I have a connection to the database, and the table I am looking for. So far so good. 🙂

The new challenge is that I can connect to the table, but there are no columns and or information visible.

So I have to sort that out.

But as I say: new challenges provide new insight.

Thanks for getting me on my way

Hein