Integrating SharePoint Spreadsheet Data into Azure SQL Database with Blob Storage Using ADF

Integrating SharePoint Spreadsheet Data into Azure SQL Database with Blob Storage Using ADF

Businesses frequently rely on a wide range of tools and resources to efficiently handle and use their data. Document management and team collaboration are two common uses for SharePoint, Microsoft's potent collaboration tool. We retrieve data from SharePoint spreadsheets and store it in SQL databases, also moving those files from their original folder to an archive folder. In contrast, the cloud-based relational database solution provided by Azure SQL Database is completely managed and scalable. Blob storage and ADF together can effectively simplify and streamline the process of integrating data from SharePoint into the database.

This blog post will explain how to use ADF pipelines and Blob Storage to merge data from Microsoft Excel spreadsheets into an Azure SQL database. Through this connectivity, businesses may combine and examine SharePoint data inside of Azure SQL Database, improving data insights and streamlining data management procedures.

Table of Contents

So, without any further interruption, let’s get started!

Step 1: Register an Application with the Microsoft Identity Platform

To apply online via the Microsoft identity platform, you must follow the below-mentioned steps:

1.1 Sign in to the Microsoft Entra admin center.

1.2 Go to App registrations in Applications.

App Registrations

1.3 Select New Registration and Add Name of New Application, for instance, TestSharePoint

New App Registration

1.4 Select Register and copy the information from the below Created application

1.4.1 Application (Client)Id

1.4.2 Directory (tenant) Id

Copy Client ID And Tenant ID

1.5 Select Certificates & secrets.

Select Certificates Secrets

1.6 Create New Client Secret

Create New Client Secret

1.7 Copy value as a Client Secrets.

Copy Client Secrets Value

1.8 Select Overview and select Tenant Name

Get Tenant Name


If you already have a site on the SharePoint domain, there's no need to create another. However, if you're setting up your first SharePoint site, click here and follow the provided steps.

Also, ensure that the user has Site Admin privileges.

Step 2: Grant SharePoint Online site permission to your registered application

The SharePoint List Online connector uses service principal authentication to connect to SharePoint. Follow these steps to set it up: Reference Link

2.1 Grant SharePoint Online site permission to your registered application by following the steps below. To do this, you need a site admin role.

2.1.1 Open SharePoint Online site link

Ex: https://[your_site_url]/_layouts/15/appinv.aspx (replace the site URL).

But Our site URL is as follows in this instance:

For instance: https://[sharepoint-domain-name][sharepoint-site]/SitePages/_layouts/15/appinv.aspx

2.1.2 Add Details.

Sharepoint Online Site Link

Note: we have to copy the data and also move that file into another folder so for app permission we have to add right as Full Control.

2.1.3 Click "Trust It" for this app.

2.2 Site Regional Settings.

Data must be obtained via Pipeline. We must establish a time zone to UTC so that it can be updated or produced in accordance with the time every fifteen minutes.

Site Regional Settings

Step 3: Create Pipeline in Azure Data Factory

Created Two Pipeline For Add data From SharePoint File To Azure SQL Database Table.

  1. Pl_sync_sharepoint_sheets
  1. Pl_manage_sheet

Here, a pipeline is being created to store spreadsheet data called CostPrice.xlsx and OrderChecks.xlsx.

3.1 Create Spreadsheet in Site with name: CostPrice.xlsx

Costprice Excel File

3.2 Create Pipeline name Pl_SharePointTrigger in ADF

3.2.1 Create Web Activity for Get Access Token Form SharePoint.

  • Url:[Tenant-ID]/tokens/OAuth/2

(Add tenantId in [Tenant-ID] that we have already copied.)

  • Method: POST
  • Headers: Content-Type: application/x-www-form-urlencoded
  • Body :


(Here Replace ApplicationId in [Client-ID], tenantId in [Tenant-ID], Client Secrets in[Client-Secret] and in Tenant Name [Tenant-Name])

3.2.2 On Succes OF Access Token Activity Create web Activity For Get All Files List From SharePoint folder.

URL : https://[sharepoint-domain][site-name]/_api/web/GetFolderByServerRelativeUrl('/sites/[site-Name]/Shared%20Documents/Data%20and%20Insights/BI/Azure%20FileShare%20Data/Cost%20Price')/Files

Header : Authorization : @concat('Bearer ', activity('generate_access_token').output.access_token)

Accept : application/json;odata=verbose

Get All Files List From Sharepoint

3.2.3 On Succes OfwebActivity Added Execute pipeline Activity.

(Here for Order Checks create one parallel web activity.)

InvockedPipeline :pl_manage_Sheet

Parameter :Result : @activity('we_fetch_cost_price_sheet').output.d.results

FolderName : Cost%20Price

AccessToken : @concat('Bearer ', activity('generate_access_token').output.access_token)

On Success Web Activity Add Pipeline

3.3 Created Pl_manage_sheetwith Parameters .

  • Results (array) :
  • FolderName(string):
  • AccessToken(string):

Created Pl Manage Sheet

  • CREATE foreach activity

Item : @pipeline().parameters.Results

Create Foreach Activity

  • IN FOREACHCreated Copy Activity.

At first, we copy the file to Azure Blob Storage from the SharePoint folder.

In Foreach Created Copy Activity

  • Select Source Dataset as http with binary.


Http With Binary

Select Source Dataset


Add Base Url : https://[domain-name]

Authentication type: Anonymous

Select New Link Service

Edit New Link Service2

Hire Developers To Integrate Sharepoint


1. Add Parameter

SheetRelativeurl (String) : Default Value if want to set then file path

e.g.: '/sites/[sharepoint-site]/Shared%20Documents/CostPrice.xlsx'

2. Add Relative Url

@concat('/sites/ [sharepoint-site] /_api/web/GetFileByServerRelativeUrl(',dataset().SheetRelativeUrl,')/$value')

3. Add Dataset Properties:

SheetRelativePath: @concat(string(''''), item().ServerRelativeUrl,string(''''))

4. Request Method: GET

5. Additional Headers:

@{concat('Authorization: Bearer ', activity(GetAccessToken).output.access_token)}

In Foreach Created Copy Activity

3.4 Click on Sink Dataset:

3.4.1 Select Azure Blob Storage with Binary.

Select Azure Blob Storage

3.4.2 Add Link Service of blob:

Add File Path.

Add Link Service Of Blob

3.4.3 Open Data Set

Add Parameter:

FileName (string) :

Data Set Add Parameter

Add Value

Data Set Add Value

3.4.4 Add Sink Dataset Properties

Data Set Add Sink

3.5Create loockup Activity Data of Blob Spreadsheet.

3.5.1 Add LookUp Activity.

3.5.2 Add New Data set Azure Blob Storage with Excel.

Add New Data Set Azure Blob Storage

3.5.3 Add file Path

3.5.4 Open Excel Data Set and set two Parameter

FileName (String):

Excel Data Set Open

Set Connection :

File Name: Add Folder Name With @dataset (). FileName

First row as header : checked.

Excel Data Set Connection

Set Lookup Dataset

FileName : @item().Name

Excel Data Set Lookup Dataset

3.5.5 On Completion of LookUpAdd If Condition

In if Condition Activity Expression:


Add If Condition

If the condition is True:

  1. Add Store Procedure with azure SQL database link service :
  2. Select Store ProcedureName : @if(equals(pipeline().parameters.FolderName, 'Cost%20Price'), '[db_Etl].[SP_InsertOrUpdateCostPrice]', '[db_Etl].[SP_InsertOrUpdateOrderChecks]')
  3. Add PeraMeter

e.g. : Json (String):


Add Perameter

Pagination In Azure Data Factory Etl Pipelines

3.6 On Success of Below Activity Set Web Activity for Move File to Archive Folder

  • URL : @concat('https:// [sharepoint-domain-name][sharepoint-site]/_api/web/getFileByServerRelativeUrl(',concat(string(''''),item().ServerRelativeUrl,string('''')),')/moveTo(newurl=',concat('''','/sites/ [sharepoint-site] /Shared%20Documents/Data%20and%20Insights/BI/Azure%20FileShare%20Data/', pipeline().parameters.FolderName,'/Archive/', substring(item().Name, 0, indexOf(item().name, '.')), '_'),formatDateTime(utcNow(), 'yyyy-MM-dd'),'.xlsx'',flags=1)')
  • Method : Post
  • Headers :

Authorization : @pipeline().parameters.AccessToken

Accept: application/json; odata=verbose

Content-Type: application/json; odata=verbose

Move File To Archive Folder

3.7 On Success Of Move File activity Create Delete Activity

A file that we copied from SharePoint to Azure Blob Storage is deleted by this action.

Select Blob excel dataset. And Add Dataset properties

FileName : @item().Name


An effective and powerful method for integrating SharePoint spreadsheet data into Azure SQL Database involves leveraging ADF with Blob Storage. This integration empowers organizations to enhance data accessibility, streamline data workflows, and bolster analytical capabilities within Azure SQL Database through automated processes. By harnessing these technologies, companies can elevate their data-driven decision-making processes, gaining heightened flexibility and expandability in managing and utilizing their data assets. This approach facilitates seamless data transfer and transformation, enabling businesses to leverage SharePoint data effectively within Azure's robust SQL Database environment.

For seamless integration and efficient management of your data assets, consider leveraging the expertise of an IT outsourcing company. Hire top-notch services from The One Technologies streamline your data processes and drive business growth.

People Also Ask

How can I integrate my SharePoint spreadsheet data into Azure SQL Database using Blob Storage and ADF?

Using ADF to manage the data movement can help you integrate data from a SharePoint spreadsheet into an Azure SQL database. Using the SharePoint Online connectors in ADF, first export your SharePoint data to Azure Blob Storage. Next, ingest this data into the Azure SQL Database.

What are the benefits of integrating SharePoint data into Azure SQL Database with Blob Storage?

Scalability within Azure's cloud environment, centralized data storage, enhanced data accessibility, and seamless analytics are all made possible by integrating SharePoint data into Azure SQL Database with Blob Storage.

Do I need specific permissions or roles to perform this integration?

Yes, in both the SharePoint and Azure systems, you will require the necessary permissions. Make sure you have the required rights in Azure Blob Storage and Azure SQL Database, as well as access to SharePoint data, to carry out data integration operations.

How can this integration improve my data management and analytics capabilities?

You can take advantage of Azure's advanced analytics tools and services to extract insightful information from your SharePoint data by integrating it with the Azure SQL Database. This integration improves analytical capabilities and simplifies data management procedures.

What steps are involved in setting up SharePoint integration with Azure SQL Database using Azure Data Factory?

The usual steps are to set up Azure Blob Storage as an intermediary storage location, configure SharePoint Online as a data source in Azure Data Factory, and then use ADF pipelines to load data into an Azure SQL database.

About Author

Jenil ShingalaJenil Shingala is currently an Associate Software Engineer (.NET) at The One Technologies, a position he has held since starting his journey in the IT industry in January 2023 as a software trainee. Throughout his career, he has exemplified a commitment to growth and development, consistently learning new skills and seeking guidance from mentors and senior colleagues. His future professional goal is to ascend to a leadership role within the field of software engineering, aspiring to be a respected figure who drives innovation and makes a positive impact on projects and teams alike.

Certified By