How to Load Data from Multiple XL Sheets to Azure SQL Tables in Azure Data Factory

Issue: How to Load Data from Multiple XL Sheets to Azure SQL Tables in Azure Data Factory.

In this article, we are going to learn how to load data from multiple, EXCEL sheets to Azure SQL Tables in the Azure Data Factory, let's start the demonstration.

How to Create a pipeline:

Open the Azure Data Factory Studio, Go to the author tab, click on pipelines, then click on New pipeline.

Click on the white working window, then go to the parameters tab, click on the + New button,  provide the name, type, and default value. 

Find and drag the ForEach loop activity, go to the settings tab, click on items and add the parameter we created earlier.

Next, go inside the ForEach loop Activity, find and drag the copy data activity, go to the source tab, click on the + New button to create a new source dataset.

Select Azure blob storage then click on continue.

Select format as Excel then click on continue.

Name your dataset, select the linked service, select the file path, select the file, Worksheet mode will be Name, select none for import schema,  then click on ok.

In the source tab click on the Open button, go inside and create a parameter.

In the parameters tab, click on the + New button, then, name the parameter, select the type, give the default values, then go to the connection tab.

In the connection tab, use the parameter which we just created.

Back to the source tab, provide the values from our ForEach loop Activity.

Once we are done with our source tab, go to the sink tab, click on the + New button to create a new Sink dataset.

Select Azure SQL database, then click on continue.

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

In the sink tab, click on the Open, go to the Parameters tab, click on the + New button and create a parameter.

Go to the connection tab and use the parameter that we just created.

Now map this parameter with our ForEach loop, then click on Debug.

Video Demo: How to Load Data from Multiple XL Sheets to Azure SQL Tables in Azure Data Factory