Problem Connecting SSIS to MySQL

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.
qvfpsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterSenior Software DeveloperCommented:
I've never had any real success connecting SSMS to MySQL. Instead I use MySQL Workbench. It's pretty much as easy to use and offers very good management features.

https://www.mysql.com/products/workbench/
qvfpsAuthor Commented:
I receive the same error message if I use the following connection string

mySqlConnectionString = "Driver={MySQL ODBC 5.3 Driver};Server=192.168.5.1;Port=1234;Database=MySQLDB;User=user;Password=password"
qvfpsAuthor Commented:
I need to import a copy of the MySQL database to SQL server for offline processing.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Russ SuterSenior Software DeveloperCommented:
Ah, there are tools that do that.

http://blogs.msdn.com/b/ssma/p/mysql.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qvfpsAuthor Commented:
I have downloaded and installed the SQL Server Migration Assistant for MySql.  I have used it to import a MySql database.  However this is something I need to automate and do on a schedule.  It allows me to export the scripts for copying the schema and creating the tables but I can not see how to export the script for the actual migration.
qvfpsAuthor Commented:
I looked in the help file which I should have done first.  Aren't they wonderful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.