Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse (2023)

  • Article
  • 6 minutes to read

APPLIES TO: Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse (1)Azure Data Factory Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse (2)Azure Synapse Analytics

This article outlines how to use Copy Activity in Azure Data Factory and Azure Synapse pipelines to copy data from SharePoint Online List. The article builds on Copy Activity, which presents a general overview of Copy Activity.

Supported capabilities

This SharePoint Online List connector is supported for the following capabilities:

Supported capabilitiesIR
Copy activity (source/-)① ②
Lookup activity① ②

① Azure integration runtime ② Self-hosted integration runtime

For a list of data stores that are supported as sources or sinks, see the Supported data stores table.

Specifically, this SharePoint List Online connector uses service principal authentication and retrieves data via OData protocol.

Tip

This connector supports copying data from SharePoint Online List but not file. Learn how to copy file from Copy file from SharePoint Online section.

(Video) DataFactory: Extract data from SharePoint Online

Prerequisites

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

  1. Register an application entity in Azure Active Directory (Azure AD) by following Register your application with an Azure AD tenant. Make note of the following values, which you use to define the linked service:

    • Application ID
    • Application key
    • Tenant ID
  2. Grant SharePoint Online site permission to your registered application by following the steps below. To do this, you need a site admin role.

    1. Open SharePoint Online site link e.g. https://[your_site_url]/_layouts/15/appinv.aspx (replace the site URL).

    2. Search the application ID you registered, fill the empty fields, and click "Create".

      • App Domain: contoso.com

      • Redirect URL: https://www.contoso.com

      • Permission Request XML:

        <AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="Read"/></AppPermissionRequests>

        Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse (3)

      Note

      (Video) SharePoint File Copy to Azure using Power Automate or Azure Data Factory

      In the context of configuring the SharePoint connector, the "App Domain" and "Redirect URL" refer to the SharePoint app that you have registered in Azure Active Directory (AAD) to allow access to your SharePoint data. The "App Domain" is the domain where your SharePoint site is hosted. For example, if your SharePoint site is located at "https://contoso.sharepoint.com", then the "App Domain" would be "contoso.sharepoint.com". The "Redirect URL" is the URL that the SharePoint app will redirect to after the user has authenticated and granted permissions to the app. This URL should be a page on your SharePoint site that the app has permission to access. For example, you could use the URL of a page that displays a list of files in a library, or a page that displays the contents of a document.

    3. Click "Trust It" for this app.

Get started

To perform the Copy activity with a pipeline, you can use one of the following tools or SDKs:

  • The Copy Data tool
  • The Azure portal
  • The .NET SDK
  • The Python SDK
  • Azure PowerShell
  • The REST API
  • The Azure Resource Manager template

Use the following steps to create a linked service to a SharePoint Online List in the Azure portal UI.

  1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:

  2. Search for SharePoint and select the SharePoint Online List connector.

    Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse (5)

  3. Configure the service details, test the connection, and create the new linked service.

    Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse (6)

    (Video) Copy Sharepoint Data to Azure Data Factory

Connector configuration details

The following sections provide details about properties you can use to define entities that are specific to SharePoint Online List connector.

Linked service properties

The following properties are supported for a SharePoint Online List linked service:

PropertyDescriptionRequired
typeThe type property must be set to: SharePointOnlineList.Yes
siteUrlThe SharePoint Online site url, e.g. https://contoso.sharepoint.com/sites/siteName.Yes
servicePrincipalIdThe Application (client) ID of the application registered in Azure Active Directory. Refer to Prerequisites for more details including the permission settings.Yes
servicePrincipalKeyThe application's key. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault.Yes
tenantIdThe tenant ID under which your application resides.Yes
connectViaThe Integration Runtime to use to connect to the data store. If not specified, the default Azure Integration Runtime is used.No

Example:

{ "name": "SharePointOnlineList", "properties": { "type": "SharePointOnlineList", "typeProperties": { "siteUrl": "<site URL>", "servicePrincipalId": "<service principal id>", "servicePrincipalKey": { "type": "SecureString", "value": "<service principal key>" }, "tenantId": "<tenant ID>" } }}

Dataset properties

For a full list of sections and properties that are available for defining datasets, see Datasets and linked services. The following section provides a list of the properties supported by the SAP table dataset.

PropertyDescriptionRequired
typeThe type property of the dataset must be set to SharePointOnlineLResource.Yes
listNameThe name of the SharePoint Online List.Yes

Example

{ "name": "SharePointOnlineListDataset", "properties": { "type": "SharePointOnlineListResource", "linkedServiceName": { "referenceName": "<SharePoint Online List linked service name>", "type": "LinkedServiceReference" }, "typeProperties": { "listName": "<name of the list>" } }}

Copy Activity properties

For a full list of sections and properties that are available for defining activities, see Pipelines. The following section provides a list of the properties supported by the SharePoint Online List source.

To copy data from SharePoint Online List, the following properties are supported in the Copy Activity source section:

PropertyDescriptionRequired
typeThe type property of the Copy Activity source must be set to SharePointOnlineListSource.Yes
queryCustom OData query options for filtering data. Example: "$top=10&$select=Title,Number".No
httpRequestTimeoutThe timeout (in second) for the HTTP request to get a response. Default is 300 (5 minutes).No

Example

"activities":[ { "name": "CopyFromSharePointOnlineList", "type": "Copy", "inputs": [ { "referenceName": "<SharePoint Online List input dataset name>", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "<output dataset name>", "type": "DatasetReference" } ], "typeProperties": { "source": { "type": "SharePointOnlineListSource", "query": "<OData query e.g. $top=10&$select=Title,Number>" }, "sink": { "type": "<sink type>" } } }]

Note

It isn't possible to select more than one choice data type for a SharePoint Online List source.

(Video) 2. Get File Names from Source Folder Dynamically in Azure Data Factory

When you copy data from SharePoint Online List, the following mappings are used between SharePoint Online List data types and interim data types used by the service internally.

SharePoint Online data typeOData data typeInterim data type
Single line of textEdm.StringString
Multiple lines of textEdm.StringString
Choice (menu to choose from)Edm.StringString
Number (1, 1.0, 100)Edm.DoubleDouble
Currency ($, ¥, €)Edm.DoubleDouble
Date and TimeEdm.DateTimeDateTime
Lookup (information already on this site)Edm.Int32Int32
Yes/No (check box)Edm.BooleanBoolean
Person or GroupEdm.Int32Int32
Hyperlink or PictureEdm.StringString
Calculated (calculation based on other columns)Edm.String / Edm.Double / Edm.DateTime / Edm.BooleanString / Double / DateTime / Boolean
AttachmentNot supported
Task OutcomeNot supported
External DataNot supported
Managed MetadataNot supported

You can copy file from SharePoint Online by using Web activity to authenticate and grab access token from SPO, then passing to subsequent Copy activity to copy data with HTTP connector as source.

Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse (7)

  1. Follow the Prerequisites section to create AAD application and grant permission to SharePoint Online.

  2. Create a Web Activity to get the access token from SharePoint Online:

    • URL: https://accounts.accesscontrol.windows.net/[Tenant-ID]/tokens/OAuth/2. Replace the tenant ID.
    • 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]. Replace the client ID (application ID), client secret (application key), tenant ID, and tenant name (of the SharePoint tenant).

    Caution

    Set the Secure Output option to true in Web activity to prevent the token value from being logged in plain text. Any further activities that consume this value should have their Secure Input option set to true.

  3. Chain with a Copy activity with HTTP connector as source to copy SharePoint Online file content:

    • HTTP linked service:
      • Base URL: https://[site-url]/_api/web/GetFileByServerRelativeUrl('[relative-path-to-file]')/$value. Replace the site URL and relative path to file. Make sure to include the SharePoint site URL along with the Domain name, such as https://[sharepoint-domain-name].sharepoint.com/sites/[sharepoint-site]/_api/web/GetFileByServerRelativeUrl('/sites/[sharepoint-site]/[relative-path-to-file]')/$value.
      • Authentication type: Anonymous (to use the Bearer token configured in copy activity source later)
    • Dataset: choose the format you want. To copy file as-is, select "Binary" type.
    • Copy activity source:
      • Request method: GET
      • Additional header: use the following expression@{concat('Authorization: Bearer ', activity('<Web-activity-name>').output.access_token)}, which uses the Bearer token generated by the upstream Web activity as authorization header. Replace the Web activity name.
    • Configure the copy activity sink as usual.

Note

(Video) #91. Azure Data Factory: Copy File From HTTP Server to ADLS

Even if an Azure AD application has FullControl permissions on SharePoint Online, you can't copy files from document libraries with IRM enabled.

Lookup activity properties

To learn details about the properties, check Lookup activity.

Next steps

For a list of data stores that Copy Activity supports as sources and sinks, see Supported data stores and formats.

FAQs

How do I extract data from SharePoint to Azure Data Factory? ›

Create a linked service to a SharePoint Online List using UI. Use the following steps to create a linked service to a SharePoint Online List in the Azure portal UI. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New: Azure Data Factory.

How do I extract data from SharePoint Online? ›

Open your team site in SharePoint Online, then open the SharePoint list you want to export. Click Export to Excel.

How do I retrieve data from a SharePoint list using API? ›

We can put the below rest api code inside a script editor web part or content editor web part in SharePoint web part page. Similarly, If you want to filter based on date, then you can write the URL like below: var requestUri = _spPageContextInfo. webAbsoluteUrl + "/_api/web/lists/getByTitle('Employee')/items/?$

What are the various data transfer options available to copy data Azure? ›

You copy data to the device and then ship it to Azure where the data is uploaded. The available options for this case are Data Box Disk, Data Box, Data Box Heavy, and Import/Export (use your own disks).

How do I import multiple CSV files into ADF? ›

Create new pipeline -> add "Data Flow" activity. in Data Flows tab -> create new Data flow. Load the csv files as a source in Data flow (look at attached picture below)

How do I copy files from SharePoint to Azure Blob Storage using ADF? ›

Azure Data Factory does have a SharePoint Online connector, but you can't use it to copy files. You must use the HTTP connector to copy files from SharePoint in Data Factory. And that requires you to make a web call first to get a bearer token to be used in the authentication for the source of the copy activity.

How do I copy data from one Azure database to another? ›

Copy using the Azure portal

To copy a database by using the Azure portal, open the page for your database, and then choose Copy to open the Create SQL Database - Copy database page. Fill in the values for the target server where you want to copy your database to.

Which of the following would be mapped to the copy activity in Azure Data factory? ›

Copy activity performs source types to sink types mapping with the following flow: Convert from source native data types to interim data types used by Azure Data Factory and Synapse pipelines.

What is the difference between Azure synapse and Azure Data factory? ›

Difference between Synapse Analytics and Data Factory

Data Factory offers the integration of different data sources, but Synapse Analytics serves as a platform from which you can manage, prepare and serve data for BI and Machine Learning purposes with reporting capabilities.

How do I get more than 5000 items from SharePoint list using REST API? ›

This is the limitation of SharePoint REST API, you cannot get more than 5000 items from single API call. You can use filtering on columns with such conditions that will return less than 5000 items (with first filter condition).

How do you solve performance problems in ADF? ›

The solution to this problem can be found in reducing the size of sessions by decreasing of the amount of data loaded and held in the session. With a low memory consumption, a more responsive, stable and scalable ADF application can be delivered.

What is the copy data activity in ADF do? ›

In Azure Data Factory and Synapse pipelines, you can use the Copy activity to copy data among data stores located on-premises and in the cloud. After you copy the data, you can use other activities to further transform and analyze it.

What are the types of copy behavior property of copy activity in ADF? ›

Copy behavior: If you are copying data from another file-based data store, copy activity provides three types of behavior via “copyBehavior” property: preserve hierarchy, flatten hierarchy, and merge files.

What are the 3 types of data that can be stored in Azure? ›

Azure storage types include objects, managed files and managed disks. Customers should understand their often-specific uses before implementation. Each storage type has different pricing tiers -- usually based on performance and availability -- to make each one accessible to companies of every size and type.

Why is ADF better than SSIS? ›

Azure Data Factory supports both batch and streaming data processes while SSIS supports only batch processes. Azure Data Factory allows you to define a series of tasks that need to be performed on data, such as copying data from one location to another, analyzing it and storing it in a database.

How many activities can run in parallel in ADF? ›

You can have only maximum 40 activities in a pipeline. If you need more, you'll need to create child pipelines and call them using the Execute Pipeline activity.

How do I import multiple Excel files into ADF? ›

If you have different sheet names in each excel file, you can create a file to add the file names and sheet names to it. Using the lookup activity, you can pull the data from the file and pass the file name and sheet name to the ForEach activity by parameterizing the sheet name in the dataset.

What is difference between Azure file share and blob storage? ›

Azure Blob is an object storage solution. It allows you to store a large amount of unstructured data, whereas Azure files permit you to develop managed file share for the cloud. Moreover, Azure file share can also be mounted by the on premises deployment of Windows, Linux, and macOS.

How to copy files from SharePoint to Azure Blob Storage using ADF? ›

Azure Data Factory does have a SharePoint Online connector, but you can't use it to copy files. You must use the HTTP connector to copy files from SharePoint in Data Factory. And that requires you to make a web call first to get a bearer token to be used in the authentication for the source of the copy activity.

How do I Connect SharePoint to Azure? ›

Sign in to the Azure Active Directory portal. Go to Enterprise applications, and then select All applications. To add a new application, select New application at the top of the dialog box. In the search box, enter SharePoint on-premises.

What is the difference between blob and file storage in Azure? ›

Azure File Storage and Blob Storage offer the same level of redundancy, but Azure Blob Storage is cheaper than File Storage. Azure File Storage provides the folder for the data storage, while Azure Blob Storage does not provide the folder. They give a flat structure for data storage.

Does SharePoint use blob storage? ›

SharePoint Server supports a BLOB storage implementation that accesses BLOB data by using the RBS APIs through such a provider.

Videos

1. Power Automate: Transfer records from SharePoint list to Azure SQL database
(Mariano Gomez)
2. Copy Documents from SharePoint to Azure Storage using Power Automate
(KeaPoint Tech Tips)
3. 32. Copy data from REST API which sends response in Pages using Azure data factory
(WafaStudies)
4. Azure Synapse Analytics: Introduction to Copy Activity [Introduction to Synapse Analytics - Ep. 10]
(Pragmatic Works)
5. 20. Get Latest File from Folder and Process it in Azure Data Factory
(WafaStudies)
6. Azure Data Factory | Loop through multiple files in ADLS Container | Lookup & ForEach Activities
(Keshav- Learn !T - Self)
Top Articles
Latest Posts
Article information

Author: Clemencia Bogisich Ret

Last Updated: 11/01/2022

Views: 5702

Rating: 5 / 5 (80 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Clemencia Bogisich Ret

Birthday: 2001-07-17

Address: Suite 794 53887 Geri Spring, West Cristentown, KY 54855

Phone: +5934435460663

Job: Central Hospitality Director

Hobby: Yoga, Electronics, Rafting, Lockpicking, Inline skating, Puzzles, scrapbook

Introduction: My name is Clemencia Bogisich Ret, I am a super, outstanding, graceful, friendly, vast, comfortable, agreeable person who loves writing and wants to share my knowledge and understanding with you.