MySQL to SQL Server Data Migration Through SSIS

MySQL to SQL Server Data Migration Through SSIS

In this post we will learn MySQL to SQL Server Data Migration Through SSIS. Here we are using SQL Server integration Services for data migration. SSIS will help us scheduling it as job to run this package on time to time. For this first we have to add DSN(Data Source Name). You can check here for DSN configuration.

In earlier post we have mentioned How to Install MySQL in Windows 7, Create Database, Table in MySQL, Select, Insert, Update, Delete Statement in MySQL.

Steps:

1. Open Business Intelligence development studio or Visual studio.

2. Create a new Integration Service project.
MySQL To SQL Server Data Transfer 1

3. Now add data flow task on control flow.

4. Now go to Data flow task, add one ado .net source and ado .net destination from toolbox.
MySQL To SQL Server Data Transfer 2

5. Double Click on ado .net source. Click on New ado.net connection Manager. Click New on new window.
MySQL To SQL Server Data Transfer 3

MySQL To SQL Server Data Transfer 4

6. Choose Odbc Data provider in provider drop down. Select DSN name you have configured. Provide username and password.
MySQL To SQL Server Data Transfer 5

7. Click on OK, On Data access Mode choose SQL Command. Write select command for table which data you want to migrate. Click on OK.
8-6-2014 9-45-31 PM

8. Double Click on ado .net destination, Click on New ado.net connection Manager. Click New on new window.

9. Choose SQLClient Data provider in provider drop down. Input Server name, Choose Authentication type, Choose database click on OK.
MySQL To SQL Server Data Transfer 7

9. Click on OK, Choose table you have created or create new, click on mappings tab. Do mappings and click on OK.
MySQL To SQL Server Data Transfer 8

10. Execute your package and your data will be transfer successfully.

MySQL To SQL Server Data Transfer 9