Export data from MySQL to SQL Server

By:   |   Comments (19)   |   Related: More > Integration Services Development


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.


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. microsoft visual studio
  4. In the toolbox drag and drop the Data flow task onto the design surface of the Control Flow tab.
  5. data flow task
  6. Double click in the Data Flow task in the Design pane.
  7. data flow task
  8. 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.
  9. destination
  10. Go to Windows start menu | Administrative tools | Data Sources (ODBC) and click the Add button.
  11. administrator
  12. Select the MySQL ODBC driver and press Finish.  Please note this driver is installed with the connector specified in the prerequisites section above.
  13. create new data source
  14. Specify the Data Source Name. e.g. "MySQL conn".
  15. Specify the TCP/IP Server.  It can be the IP or the localhost if the machine used is the local machine.
  16. Specify the user, in this case root and the password.  Ask to the MySQL administrator if you do not know the user database password).
  17. Select the MySQL database.
  18. connector
  19. Congratulations! You have a ODBC connection. Now let's use it in SSIS and return to the SSIS project.
  20. Double click in the ADO Net Source and press the new button.
  21. source editor
  22. Press the new button again to add a connection.
  23. connection manager
  24. 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.
  25. connection manager
  26. In the ADO.NET source editor, in Data access mode, select SQL Command.
  27. 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.
  28. source editor
  29. Double click in the ADO.NET Destination task and in the Connection manager press New.
  30. destination editor
  31. In the Configure ADO.NET Connection Manager press new again.
  32. manager
  33. 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.
  34. adventure works
  35. In the ADO.NET Destination Editor, click new in the Use a table or view option.
  36. destination editor
  37. In the Create Table box, use this code:
  38.  CREATE TABLE "myTable" ( 
      "myColumn" nvarchar(20) 
    create table
  39. In the ADO.NET Destination Editor, click the Mapping page and press OK.
  40. destination editor
  41. We are ready. Press the Start Debugging icon as shown below.
  42. start debugging
  43. You will see the tasks in green which means the tasks were completed successfully with the associated row count.
  44. net destination
  45. 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.
  46. management studio
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, April 7, 2019 - 4:51:10 AM - rrr Back To Top (79494)

Thank you very much Mr. Daniel your article is very helpful, clearly explained

Wednesday, June 3, 2015 - 12:48:13 PM - Saba Back To Top (37371)

Thanks alot for posting such a neat and useful tip. The screen shots helped alot. Thank you very much!

Wednesday, March 11, 2015 - 11:47:40 PM - Muhammad Azamuddin Back To Top (36502)

I use a simple workaround using MS Access as tunel between MySQL and MSSQL, I successfully migrate my MySQL data into MSSQL on Windows server 2003. The data contains binary type too.


You can read my post at http://azamudd.in/convert-data-from-mysql-into-mssql-the-easy-way-via-ms-access/

Tuesday, February 10, 2015 - 2:55:03 PM - Jane Back To Top (36196)

Hi Daniel:

We have SQL server 2008R2. Does that mean the Bids 2008 is 32 bit and I have to download MySQL ODBC connecter for Windows (32 bit)? Thank you very much.




Friday, December 19, 2014 - 12:49:04 PM - Daniel Back To Top (35688)

Use the Windows Driver.

Friday, December 19, 2014 - 9:23:43 AM - Jane Back To Top (35681)

Hi Daniel,


Thanks for your positive feedback. When I download the MySQL ODBC connecter, do I download the version for Windows for the version for Linux? ( I am going to move MySQL database installed in Linux to Microsoft SQL server 2008R2).


Thanks again and Happy holidays,




Thursday, December 18, 2014 - 4:36:31 PM - Greg Robidoux Back To Top (35672)

Hi Jane,

Daniel updated his comment as shown below. This is possible:


The steps are the same, but you may need to read Linux articles related to unblock ports and security permissions in Linux. But it is possible to connect mySQL in Linux with SSIS.

Wednesday, December 17, 2014 - 3:11:44 PM - Jane Back To Top (35647)

Hi Daniel: Thanks for your quick response. Do you mean it is impossible to use SSIS tool to load data from MySQL database (installed in Linux) to Microsoft SQL server 2008R2? Thanks





Wednesday, December 17, 2014 - 2:51:15 PM - Daniel Back To Top (35646)
The steps are the same, but you may need to read Linux articles related to unblock ports and security permissions in Linux. But it is possible to connect mySQL in Linux with SSIS.

Wednesday, December 17, 2014 - 2:19:04 PM - Jane Back To Top (35644)

Hi Daniel: Thanks for your detailed instruction. If MySQL database is installed in Linux, do I still follow the same steps? Thank you again for your great article.


Thursday, October 23, 2014 - 12:24:05 PM - Danny Back To Top (35045)


I can not see ADO.Net Editor in data flow task..

Can someone guide me how to install or Add on in SSIS?

I am using BIDS 2005 

Wednesday, September 24, 2014 - 12:13:58 PM - msu Back To Top (34701)

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.





Friday, October 19, 2012 - 4:26:42 AM - Hari Priya Back To Top (19993)

very helpful.. very clearly explained..

Saturday, August 11, 2012 - 11:29:44 AM - Dmitry Back To Top (19000)

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

Thursday, August 2, 2012 - 3:11:16 AM - Damir Bulic Back To Top (18878)

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 Back To Top (18568)

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

Wednesday, March 21, 2012 - 3:10:04 AM - Santosh S.Pawar Back To Top (16553)

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

Tuesday, March 20, 2012 - 9:20:50 AM - Daniel Calbimonte Back To Top (16532)

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


Tuesday, March 20, 2012 - 2:44:27 AM - Nirav Gajjar Back To Top (16523)

HI Daniel,


Really very nice post.

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

get free sql tips
agree to terms