ADhal37
asked on
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.Conne ctionInfo)
-------------------------- ----
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
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.Conne
--------------------------
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
ASKER
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
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
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that's briiliant! works now
Thanks very much
Anita
Thanks very much
Anita
http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/