Many organizations have the need to load data from Excel spreadsheets into SQL Server databases. In the past, this process required the use of tools such as VBA, SSIS, or C#. However, Microsoft has recently introduced an Excel connector for Azure Data Factory, which simplifies the data ingestion process. In this article, we will explore how to dynamically load Excel files with multiple tabs into SQL Server tables using Azure Data Factory.

Pre-Requisites

Before we begin, make sure you have the following:

• An Excel spreadsheet with multiple tabs
• An Azure Data Lake Storage Gen2 account
• An Azure SQL Database

Step 1: Upload the Excel Spreadsheet to Azure Data Lake Storage Gen2

First, upload the Excel spreadsheet containing multiple tabs to your Azure Data Lake Storage Gen2 account. This will serve as the source for our data.

Step 2: Create Linked Services and Datasets

In Azure Data Factory, create a linked service for your Azure Data Lake Storage Gen2 account. This will allow you to access the Excel spreadsheet.

Next, create a dataset for the Excel spreadsheet. Make sure to configure the dataset to include the sheet name as a dynamic parameter. Also, enable the “First row as header” option if your spreadsheet has headers.

Create a sink dataset for your target Azure SQL table. This dataset should be connected to your Azure SQL Database.

Step 3: Load Multiple Excel Sheets into a Single SQL Server Table

To load multiple Excel sheets into a single SQL Server table, create a pipeline in Azure Data Factory.

Add a ForEach loop activity to the pipeline canvas. Configure the loop to iterate over the sheet names in the Excel spreadsheet.

Within the loop activity, add a CopyActivity to copy the data from each sheet into the SQL Server table. Make sure to set the table option to “Auto Create Table” if the table does not exist.

After executing the pipeline, you will see that all the sheets from the Excel spreadsheet have been loaded into the SQL Server table.

Step 4: Load Multiple Excel Sheets into Multiple SQL Server Tables

If you want to load each Excel sheet into a separate SQL Server table, follow these steps:

Create a lookup table in your SQL Server database that maps each sheet name to a table name.

Create a dataset for the lookup table in Azure Data Factory. Configure the dataset to include the sheet name as a parameter.

In the Azure SQL Database dataset connection, add a parameter for the table name.

In the pipeline, add a lookup activity to retrieve the table name for each sheet from the lookup table.

Within the ForEach loop activity, add a CopyActivity to copy the data from each sheet into the corresponding SQL Server table. Make sure to set the table option to “Auto Create Table” if the table does not exist.

After running the pipeline, you will see that each sheet from the Excel spreadsheet has been loaded into a separate SQL Server table, based on the mappings defined in the lookup table.

By following these steps, you can easily load Excel files with multiple tabs into SQL Server using Azure Data Factory. This provides a streamlined and efficient way to ingest data from Excel spreadsheets into your SQL Server databases.

Article Last Updated: 2021-07-06