Load Excel File with Multiple Sheets Dynamically by using Lookup Activity in Azure Data Factory

Issue: How To Load Excel File with Multiple Sheets Dynamically by using Lookup Activity in Azure Data Factory.

In this article, we are going to learn how to load Excel file with multiple sheets dynamically by using lookup activity in the Azure data factory. in this scenario I have an Excel file containing three (03) sheets, now I have to load the two sheets from the file to the Azure Data Factory, and the third one will be ignored, let's start our demonstration.

Open the Azure data factory studio, go to the Author tab, click on pipelines, then click on New pipeline.

Find and drag the Lookup Activity, then go to the source tab, click on the + New button to create a new source dataset.

Select Azure SQL database, then click on continue.

Name your dataset and then create a linked service, name your linked service, select the Azure subscription, select the server name, select the database, select the authentication type, provide the user name and password, then click on create, and then click on ok

In the settings tab, click on the query and write the query as we need sheet names, and then copy 2 sheets from the source.

Find and drag the ForEach loop activity, connect with the lookup activity, go to the settings tab then add the dynamic content from our lookup activity, and add ''.value''.

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

Select Azure blob storage, then click on continue.

Select Excel, then click on continue.

Name your dataset, select the linked service, provide the file path and file name, worksheet mode will be Name, Select First row as header, select None for import schema, then click on Ok. 

In the source, tab click on the Open button, then go to the parameters tab and create a new parameter, then go to the connection tab and use the parameter.

  

In the source, tab add the dynamic content for sheets names.

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

Select Azure SQL database, then click on continue.

Name your Sink dataset, select the linked service, select None for import Schema, then click on Ok.

In the Sink tab, click on Open, then go to the parameters tab, click on the + New button then create a parameter and use it in the connections tab.

In the Sink, tab add the dynamic content, mapping with the ForEach loop Activity, and then click on Debug.

Video Demo: Load Excel File with Multiple Sheets Dynamically by using Lookup Activity in Azure Data Factory