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

• Register an application with the Microsoft Identity Platform
• Grant SharePoint Online site permission to your registered application
• Create Pipeline in Azure Data Factory
• Conclusion
• People Also Ask

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.

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

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

1.5 Select Certificates & secrets.

1.6 Create New Client Secret

1.7 Copy value as a Client Secrets.

1.8 Select Overview and select Tenant Name

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.com/sites/[sharepoint-site]/SitePages/_layouts/15/appinv.aspx

2.1.2 Add Details.

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.

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

3.2 Create Pipeline name Pl_SharePointTrigger in ADF

3.2.1 Create Web Activity for Get Access Token Form SharePoint.

• Url: https://accounts.accesscontrol.windows.net/[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 :

grant_type=client_credentials&client_id=[Client-ID]@[Tenant-ID]&client_secret=[Client-Secret]&resource=00000003-0000-0ff1-ce00-000000000000/[Tenant-Name].sharepoint.com@[Tenant-ID]

(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].sharepoint.com/sites/[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

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)

3.3 Created Pl_manage_sheetwith Parameters .

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

• CREATE foreach activity

Item : @pipeline().parameters.Results

• IN FOREACHCreated Copy Activity.

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

• Select Source Dataset as http with binary.

OPEN HTTP DATASET

• SELECT NEW LINK SERVICE.

Add Base Url : https://[domain-name].sharepoint.com/

Authentication type: Anonymous

• Open-SOURCE DATASET

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)}

3.4 Click on Sink Dataset:

3.4.1 Select Azure Blob Storage with Binary.

3.4.2 Add Link Service of blob:

Add File Path.

3.4.3 Open Data Set

Add Parameter:

FileName (string) :

Add Value

3.4.4 Add Sink Dataset Properties

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.

3.5.3 Add file Path

3.5.4 Open Excel Data Set and set two Parameter

FileName (String):

Set Connection :

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

First row as header : checked.

Set Lookup Dataset

FileName : @item().Name

3.5.5 On Completion of LookUpAdd If Condition

In if Condition Activity Expression:

@contains(activity('lp_retrieve_file_from_blob').output,'value')

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):

@string(activity('lp_retrieve_file_from_blob').output.value)

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

• URL : @concat('https:// [sharepoint-domain-name].sharepoint.com/sites/[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

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

Conclusion

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 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.