export mysql Database(remote VM) to local SQL Server 2008

Question

 the first problem i met is using data connection in visual studio 2010 to get a database located in remote machine. So i add connection and choose data source .NET Framework Data Provider for ODBC, and get the connection string, which i had add before in my ODBC Manager(with name and passwort of course), and then i click test connection, it is successful, problem is i can not open tables, i can not see the rows and columns. i tested a connection with northwind.sdf, there was no problem.

so i tried to export the same database(mysql data in remote VM) to my local SQL Server 2008

 i use MS Sql server 2008 import and Export Data(32-bit) Wizard, choose a data Source: Net Framework Data Provider for ODBC, destination: SQL Server Native Client 10.0 Local Server , it has error: Can not locate Mapping files.

HELP!!!!!!

Answer 1

Hi Sharlene,

SSIS uses xml files to map data types. Kept in "Program Files\Microsoft SQL Server\100\DTS\MappingFiles" so it says that Import and Export wizard was unable to find the mapping files for MySQL and SQL Server. You may try to re-install the mysql provider or just copy xml mapping file form other computer.

See: http://msdn.microsoft.com/en-us/library/ms141209.aspx

Have a look: http://www.packtpub.com/article/mysql-data-transfer-using-sql-server-integration-services-ssis

See: http://blogs.msdn.com/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

Final: http://www.ideaexcursion.com/2009/06/04/howto-connect-to-mysql-in-ssis/

Hope that helps.

Onur

---

BI and ERP Senior Consultant @ Nexum Bogazici
If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

Will be presenting "Rapid Report Developing with SSRS 2008 R2 and ReportBuilder 3.0" on Microsoft Launch 2010; Istanbul - Turkey on May 18, 2010. Link

Answer 2

Hi Onur,

thanks  for your help.

the problem is solved. i installed a mysql connector for .NET to connect the remote database, it works well so far.