Importing data into SQL server 2008 R2 from MYSQL 5.5

Hi,

I'm new to MYSQL and need to export data from MYSQL into SQL Server.  I have tried downloading a ODBC driver onto the machine however I get an architecture mis match message when trying to use SSIS to create the connection or using import/export method from SQL Server.

I have also tried using a linked server however get this message:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "CODA_IMPACT".
OLE DB provider "MSDASQL" for linked server "CODA_IMPACT" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

Can you please advise what the best method is for this.  I am a SQL server DBA  and have very little experience of MYSQL!

Many Thanks in advance
Anita
ADhal37Asked:
Who is Participating?
 
x-menConnect With a Mentor IT super heroCommented:
on the import wizard, instead of chosing "copy from table..." choose "write a query"
0
 
x-menIT super heroCommented:
export to CSV and then import on SQL Server.

http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/
0
 
ADhal37Author Commented:
Thanks! This is a very interesting post and I have learmt a few things regarding MYSQL.

Is there a direct way to do this?  We would like to avoid additional steps if possible, hwoever this is a very good alternative!

Anita
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
x-menIT super heroCommented:
if by "direct way" you meen automation, if you cant get the connectors to SSIS, and dont what or cant use / buy 3rd party apps. schedule the export on one side, to a share, and schedule the import on the SQL Server side.
0
 
ADhal37Author Commented:
Sorry, I meant, direct from MYSQL to SQL server via ODBC/Linked Server/SSIS etc.

I have tried following some posts on creating linked servers and ODBC connections but come across errors.  The closest we have got is my colleague using import/exprt tool and ODBC from SQL server and getting an error (see attached word doc.
errorimport.docx
0
 
ADhal37Author Commented:
that's briiliant! works now

Thanks very much
Anita
0
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.

All Courses

From novice to tech pro — start learning today.