Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Export data from MySQL to SQL Server

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (8)   |   Related Tips: More > Integration Services Development
Problem

Many people want to create a customized migration process using SQL Server Integration Services (SSIS), but it is hard to start working with heterogeneous databases like MySQL, Postgres, DB2, etc. Check out this tip to learn about how to import data from heterogeneous databases like MySQL to SQL Server.

Solution

In this example, the database used to import data from is the Test Database in MySQL which is installed by default. We will import data from MySQL to SQL Server using SQL Server Integration Services.

MySQL Prerequisites

To follow the steps in this tip it is necessary to have the following software installed:

Let's create a table in MySQL called myTable with a column called myColumn then insert some data in the table:

use MySQL;
create table myTable(myColumn varchar(20));
insert into myTable(myColumn) values("John");
insert into myTable(myColumn) values("Jane");
insert into myTable(myColumn) values("Arnold");
insert into myTable(myColumn) values("Jessica");

Using SQL Server Integration Services to import data from MySQL to SQL Server

Here are the steps to create the SSIS project:

  1. Start SQL Server Business Intelligence Development Studio and start an Integration Service Project.

  2. Create a new project and select Integration Services Project.


  3. In the toolbox drag and drop the Data flow task onto the design surface of the Control Flow tab.


  4. Double click in the Data Flow task in the Design pane.


  5. In the Data Flow tab, drag and drop the ADO.NET Source and ADO.NET Destination to the design pane, join both tasks with the green arrow.


  6. Go to Windows start menu | Administrative tools | Data Sources (ODBC) and click the Add button.


  7. Select the MySQL ODBC driver and press Finish.  Please note this driver is installed with the connector specified in the prerequisites section above.


  8. Specify the Data Source Name. e.g. "MySQL conn".

  9. Specify the TCP/IP Server.  It can be the IP or the localhost if the machine used is the local machine.

  10. Specify the user, in this case root and the password.  Ask to the MySQL administrator if you do not know the user database password).

  11. Select the MySQL database.


  12. Congratulations! You have a ODBC connection. Now let's use it in SSIS and return to the SSIS project.

  13. Double click in the ADO Net Source and press the new button.


  14. Press the new button again to add a connection.


  15. This is important, in the provider, select the .NET Providers\ODBC Data Provider. The ODBC connection will be displayed. Select the connection created in step 8 and press OK.


  16. In the ADO.NET source editor, in Data access mode, select SQL Command.

  17. In the SQL command test, write "select * from myTable" and press OK.  In this step you are writing the query to access to MySQL table created at the beginning.


  18. Double click in the ADO.NET Destination task and in the Connection manager press New.


  19. In the Configure ADO.NET Connection Manager press new again.


  20. In the connection manager specify the SQL Server instance name (in this example the localhost is a dot) and select a Database where you want to import the MySQL Database and press OK.  In this example the Adventureworks database is used, but any database can be used instead.


  21. In the ADO.NET Destination Editor, click new in the Use a table or view option.


  22. In the Create Table box, use this code:
            CREATE TABLE "myTable" (
                "myColumn" nvarchar(20)
            )
     



  23. In the ADO.NET Destination Editor, click the Mapping page and press OK.


  24. We are ready. Press the Start Debugging icon as shown below.


  25. You will see the tasks in green which means the tasks were completed successfully with the associated row count.


  26. Last, but not least, open the Microsoft SQL Server Management Studio and verify in the instance and database used that the new table myTable was created and also that it contains the data specified.


Next Steps


Last Update: 3/16/2012


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, September 24, 2014 - 12:13:58 PM - msu Read The Tip

Good post Daniel

 

I was using MySQL workbench for doing this but now my employer has purchased one commercial tool Data Loader which is good at it and doing the conversion job quite efficiently.

 

Thanks,
Msu

 

 


Friday, October 19, 2012 - 4:26:42 AM - Hari Priya Read The Tip

very helpful.. very clearly explained..


Saturday, August 11, 2012 - 11:29:44 AM - Dmitry Read The Tip

As well, check out DBConvert tools from http://dbconvert.com which allows converting structure, tables, views 


Thursday, August 02, 2012 - 3:11:16 AM - Damir Bulic Read The Tip

That's a lot of steps for converting data. Our Full Convert Enterprise costs $299, but makes it extremely easy to copy tables, indexes, foreign keys, and of course data. Plus it can customize everything on the fly and schedule recurring conversions.

Regarding objects migration, we have another tool - SqlTran MySQL to SQL Server. It can convert thousands of objects in just a few seconds.

These are commercial tools, but I hope readers of this blog will find my comment useful.


Tuesday, July 17, 2012 - 6:31:58 AM - Alejandro Afonso Spinola Read The Tip

Great information! It has been really helpful. Thank you for sharing!


Wednesday, March 21, 2012 - 3:10:04 AM - Santosh S.Pawar Read The Tip

 Thanks Daniel Calbimonte this information is very usefull specilly me.. 


Tuesday, March 20, 2012 - 9:20:50 AM - Daniel Calbimonte Read The Tip

To migrate objects, it is better to use migration tools like the SSMA:

http://www.microsoft.com/sqlserver/en/us/product-info/migration-tool.aspx#MySQL


Tuesday, March 20, 2012 - 2:44:27 AM - Nirav Gajjar Read The Tip

HI Daniel,

 

Really very nice post.

Is this possible to create objects from mysql to sql server like table,views,stored procedure stc....




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.