• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1217
  • Last Modified:

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
0
ckercher
Asked:
ckercher
  • 4
  • 4
  • 2
  • +1
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now