Learn more about SQL Server tools


Latest from MSSQLTips

Export data from MySQL to SQL Server

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (18)   |   Related Tips: 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. 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

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, June 03, 2015 - 12:48:13 PM - Saba Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Use the Windows Driver.

Friday, December 19, 2014 - 9:23:43 AM - Jane Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip
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 Read The Tip

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 Read The Tip


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 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.





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:


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....

More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.