I am running Microsoft SQL Server 2008r2 on a Windows 2008r2 server. I am trying to connect to a MySQL database on a separate server.
I have downloaded both the 5.2 and the 5.3 ODBC drives for MySQL.
I can create an ODBC connection in Admistrative Tools->Data Sources and when I click the test button it says it was successful. I then go into SQL Server Business Intelligence Development Studio either create a new Integration Services Project or open an empty one. From here I am unable to connect to the MySQL database.
I have tried going to the tools menu and selecting connect to database. MySQL or the DSNs I created do not appear in the Data source window so I select Microsoft ODBC Data Source (.NET Framework Data Provider for ODBC) and continue. Under Data Source Specification I look for the DSN I created. I am unable to see any System DSNs only User DSNs are visible. I select the data source I just created and tested in Data Sources from the drop down list , click test and I receive the following error message
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
I have also tried just selecting Data Connections in the Project and selecting new and following the above steps with the same result.
The server is a 64 bit server and I downloaded and installed the 64 bit versions of the ODBC drivers.