How to Create & Load Tables in Azure SQL Database from On-Prem SQL Server Database

 Issue: How to Create & Load Tables in Azure SQL Database from On-Prem SQL Server Database.

In this article, we are going to learn, how to crate and load tables in Azure SQL database from on-prem SQL server database, lets start our demonstration.

How to connect On-Prem Azure SQL database with Azure SQL database:

Open your SQL Server Management Studio and connect with the Azure SQL database, Provide the server name, then select the authentication type, then provide your user name & password and then click on connect.

Once we are connected with the Azure SQL database, go to the Azure Data factory studio, then go to the author and then click on + sign to create a new pipeline, then find and drag the lookup activity, then click on settings and then click on + New to create a new source dataset, from where we will read the list of tables which we have to copy to our Azure SQL database.

Find and select the SQL server and then click on continue.

Name the dataset, then select the linked service, select none for import schema and then click on ok

Next, in the settings tab click on the query, and write the query to obtain list of the tables.

Next, find and drag the ForEach loop activity, then connect with the Lookup activity, then go to the settings tab and click on ''Add dynamic content''.

Now, go inside the ForEach loop activity by clicking on a Pencil sign on the ForEach loop activity then find and drag the copy data activity, then go to the source tab and then click on + New button to create a new source dataset.

Find and select the SQL server and then click on continue.

Name the dataset, then select the linked service, select none for import schema, and then click on ok.

Next, in the source tab click on the Open button to create a parameter as we dont select the table name because ww have to rad the all tables from our on-prem, in that case we have to create a parameter and that parameter we will use in our connection. go to the parameter tab then click on + New, then create a parameter.

Then in the connection tab, provide the schema name then select the parameter which we have created and then go to the pipline.

Now go to the sink tab, and click on + New button to create a new sink dataset.

 

Select Azure SQL database and then click on continue.

Name the dataset, then select the linked service, select none for import schema, then click ok.

Now, create a parameter and provide the values for the sink datset set where we will write our files and then debug the pipeline.

Video Demo: How to Create & Load Tables in Azure SQL Database from On-Prem SQL Server Database