sql server 2005 ssis import does not recognize new MYSQL System DSN odbc

Hello,
We are migrating from a MySQL database to a SQL Server 2005 database. There are about 50+ tables in the MySQl DB. I am trying to use SSIS on the SQL Server 2005 server to migrate the tables from MySql to SQL Server 2005. I recently added a new MySQL system DSN ODBC on the SQL SErver 2005 Windows system. When I run the Import/Export tool on the SQL Server 2005 server the new system DSN is not ilsted as a choice of data sources. Here is the procedure I am using

1) SQL Server Manager Studio
2) Connect to a Database Engine
3) Expand Databases
4) Right-Click the DB I want to import to -> Tasks -> Import Data
5) This is where the problem is. The newly added MYSQL data source is not listed to select. I have rebooted since installing the DS. It is configured correctly because it verifies during the test.

Please, any ideas on resolving this issue are greatly appreciated.
Joe
ckercherAsked:
Who is Participating?
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.

tigin44Commented:
You should install mysql server odbc connection drivers to your server. Check that is installed or not.
0
ckercherAuthor Commented:
The MySQL drivers are installed and the DSN tests ok.
0
tigin44Commented:
from managment studio you can not use all the properties of SSIS packages. Try to design an SSIS package by using visual studio integration services interface. There you will see  all the connections and use them.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PedroCGDCommented:
try to open the ODBC using 32bits file...
the problem is because you are in a 64bit server and the ODBC is not listed... I was with same problem...
Regards
Pedro
www.pedrocgd.blogspot.com
0
HoggZillaCommented:
This thread seemed to have some potentially helpful advice.
http://lists.mysql.com/myodbc/11233 
0
HoggZillaCommented:
Migrate MySQL to Microsoft SQL Server
This uses an ODBC Connector
0
ckercherAuthor Commented:
I forgot to mention the the server is a 64 bit server and I downloaded the 64-bit driver. I'll check the links you guys mentioned. I would like to be able to do the job with wizards if I can. Thanks all.
0
PedroCGDCommented:
Check my tip... I already had this error...
Regards
0
ckercherAuthor Commented:
Pedro,
I am not sure what you are trying to say here. My Windows server is a 64-bit machine. When I install the 32-bit driver then try to add a system DSN it is not listed. When I install the 64-bit driver then try to create a system DSN it is listed and test ok when I create the new system dsn.
Joe


>try to open the ODBC using 32bits file...
>the problem is because you are in a 64bit server and the ODBC is not listed... I was with same problem...
>Regards
0
HoggZillaCommented:
Please confirm where you are with this problem. Thanks.
0
ckercherAuthor Commented:
The status as of today, Monday, is I still am unable to migrate the data from Mysql (version 5.1.11) to Sql Server 2005. The sql server system is a 64-bit machine. I tried using the 32-bit as well as the 64-bit version of the MySql driver to no avail. After installing the MySQL driver I R-clicked on the sql server DB that I want to import the data to but the MYSQL driver nor the DSN that I created is not listed as a data source.

I tried using the .Net Framework Data Provider for ODBC using the connection string for the MYSQL database

Dsn=cigaradv;Driver={MySQL ODBC 5.1 Driver};server=mysqlServer;database=theDB;user=theUser;password=thePwd

and get an error

The operation could not be completed. ERROR [IM002] [MS][ODBC Driver Manager] Data source name not found and no default driver specified.

I tried going into MYSQL and backing up the schema the a .sql file to then copy to the sql server system and running there. My first attempt produced a file with incompatable sql statements.  I'll try this again using different backup options.

I also tried using a method from a post linked to above to add a linked server. hee is the sql I used to create the linked server

EXEC master.dbo.sp_addlinkedserver
@server = N'MysqlServer',
@srvproduct=N'MySQL',
@provider=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=MysqlServer; DATABASE=theDB; USER=theUser; PASSWORD=thePwd; OPTION=3'

Then when I browse to view the tables in the mysql DB I get the error

The OLE DB provider "MSDASQL" has not been registered. (MS SQL Server, Error; 7403)

I'm hoping I can create a backup .sql file with sql statements that are compatable in sql server.
Any ideas are greatly appreciated.
Joe
0

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
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.