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.
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.
5. Double Click on ado .net source. Click on New ado.net connection Manager. Click New on new window.
6. Choose Odbc Data provider in provider drop down. Select DSN name you have configured. Provide username and password.
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. 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.
9. Click on OK, Choose table you have created or create new, click on mappings tab. Do mappings and click on OK.
10. Execute your package and your data will be transfer successfully.
