Migrating Data from On-Premises SQL Server to Azure SQL Database by using SSIS

Author – Harshit Jain, Associate Data Engineer

Data Migration through SQL Server Integration Services (SSIS) is done using data flow task in its Integration services pane because it can read data from one or more sources also transforms the data when it is in the memory and write it out against one or more destinations

And to do so we can use various connection managers like OLEDB Connection Manager or ADO.NET Connection Managers

This tutorial explains the migration from On-Premises SQL Server to Azure SQL Database using SSIS & OLEDB Connection Managers

OLE DB Connection Managers is used to connect the source & destination databases because the SSIS OLEDB destination editor provides us the choice to select the existing table(s), views, or we can create a new table.

Prerequisites

To step through this tutorial, you need the following things:

1. SQL Server Integration Services (SSIS) – SSIS is a component of SQL Server and requires a licensed version or the developer or evaluation version of SQL Server.
2. Visual Studio(optional).
3. SQL Server Data Tools for Visual Studio (SSDT).
4. This tutorial connects to an On-Premises SQL Server and loads data into Azure SQL Database so you must have to have permission to connect, to create a table, and to load data.
5. A firewall rule if you are loading data into SQL Database. You must create a firewall rule on SQL Database with the IP address of your local computer before you can upload data to the SQL Database.

Steps to perform this task:        

1. Firstly, deploy two databases named “mydb1database” on On-premises Azure SQL VM & “mydb2database” on Azure SQL Database

Mydb1database has a sample AdventureWorksLT database

Mydb2database is an empty Azure SQL Database

Create a new Integration Services project

• Launch Visual Studio.
• On the File menu, select New > Project.
• Navigate to the Installed > Templates > Business Intelligence > Integration Services project types.
• Select Integration Services Project. Provide values for Name and Location, and then select OK.

Visual Studio opens and creates a new Integration Services (SSIS) project.

Then Visual Studio opens the designer for the single new SSIS package (Package.dtsx) in the project. You see the following screen areas:

• On the left, the Toolbox of SSIS components.
• In the middle, the design surface, with multiple tabs. You typically use at least the Control Flow and the Data Flow tabs.
• On the right, the Solution Explorer and the Properties panes.

2. Create the basic data flow

• Drag a Data Flow Task from the Toolbox to the center of the design surface (on the Control Flow tab).
• Double-click the Data Flow Task to switch to the Data Flow tab.
• From the Other Sources list in the Toolbox, drag an OLE DB Source to the design surface. With the source adapter still selected, change its name to SQL Server source in the Properties pane.
• From the Other Destinations list in the Toolbox, drag an OLE DB Destination to the design surface under the OLE DB Source. With the destination adapter still selected, change its name to SQL destination in the Properties pane.

3. Configure the source adapter

Double-click the source adapter to open the OLE DB Source Editor and connect On-Premises Server using OLE DB Connection using the public IP Address of the VM or you can use your private IP within your organization.

Note:  Make sure to add your IP Address to the firewall rules of your server otherwise it will give a Network-related instance error.

• For Provider, select the SqlClient Data Provider.
• For the Server name, enter the IP Address of your server
• In Log on to the server section, select or enter authentication information.
• In Connect to a database section, select the AdventureWorks sample database.
• Click Test Connection.
• In dialog box that reports the results of the connection test, click OK to return to the Connection Manager dialog box.
• In Connection Manager dialog box, click OK to return to the Configure OLE DB Connection Manager dialog box.
• In Configure OLE DB Connection Manager dialog box, click OK to return to the OLE DB Source Editor
• In OLE DB Source Editor, in the Name of the table or the view list, select the Sales. “sales.customer”

4. Configure Destination

 Now connect your target database using the same steps as above and use the server name with the below format :

<yourservername>.database.windows.net

And select OK

Destination editor:

5. Now after connecting the source & destination databases, we will select the table which we want to Migrate so after selecting the table “sales.customer” from the source and then we will add a T-SQL command to create the table with an equal number of rows as source table on destination editor

The T-SQL command used:

6. Run the package and the package will be executed successfully.

Now suppose if you want to sync your data or insert new data at the target database every time you insert new records in your source server then it can happen using the lookup transformation task from the control flow pane

Steps to perform Data sync of two tables in two different servers (Update & Insert):

1. Open SQL Server Data Tools and go to your previously created integration project
2. Drag a new Data Flow Task from the SSIS toolbox to the Control Flow
3. Right-click on the newly created Data Flow Task and rename it to ‘SSIS LOOKUP TRANSFORMATION’
4. Now create a copy of your previously created OLE DB source to drag it into ‘SSIS LOOKUP TRANSFORMATION’ and configure it to provide the name of the table or view
5. Now, add a Lookup task in the control flow and join the Source Data task with this Lookup task using a green arrow.
6. To configure the Lookup task, double click on it, and it opens the Lookup Transformation Editor.
7. In Lookup Transformation Editor, we specify the connection manager and cache type.
8. In the drop-down of Specify how to handle rows with no matching entries, select the Redirect rows to no match output
9. Click on Connections and specify the destination table from the drop-down.
10. Click on the Columns, and it shows the source and destination table.
11. Now drag an OLEDB destination from the SSIS Toolbox and join the Lookup task with the OLEDB destination
12.  Click on START to execute the package
13. To add an update task, drag an OLE DB Command task and join the Lookup transformation in SSIS for Lookup Match Output.
14. Rename the OLE DB Command task as “Update Records”

1.  Double click on Update Records, and it opens the Advanced Editor. In the Connection Manager, select the connection from the drop-down.
2.  In the next tab, Column Mappings, map the input and destination column and click Ok

17. Execute the SSIS package and you will see the destination database is updated if you can see the same as below

 

This is the end of this article where we successfully migrated our relational data from On-Premises SQL Server to Azure SQL Database and added the update and insert task to our destination databases.