Export data from MySQL to SQL Server

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. microsoft visual studio
  3. In the toolbox drag and drop the Data flow task onto the design surface of the Control Flow tab.
    4. data flow task
  4. Double click in the Data Flow task in the Design pane.
    5. data flow task
  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. destination
  6. Go to Windows start menu | Administrative tools | Data Sources (ODBC) and click the Add button.
    7. administrator
  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. create new data source
  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. connector
  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. source editor
  14. Press the new button again to add a connection.
    15. connection manager
  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. connection manager
  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. source editor
  18. Double click in the ADO.NET Destination task and in the Connection manager press New.
    19. destination editor
  19. In the Configure ADO.NET Connection Manager press new again.
    20. manager
  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. adventure works
  21. In the ADO.NET Destination Editor, click new in the Use a table or view option.
    22. destination editor
  22. In the Create Table box, use this code:
    23.  CREATE TABLE "myTable" ( 
  "myColumn" nvarchar(20) 
)
    create table
  23. In the ADO.NET Destination Editor, click the Mapping page and press OK.
    24. destination editor
  24. We are ready. Press the Start Debugging icon as shown below.
    25. start debugging
  25. You will see the tasks in green which means the tasks were completed successfully with the associated row count.
    26. net destination
  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.
    27. management studio

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *