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

APPLIES TO: Azure Data Factory 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:

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

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.

   See Also

   Herunterladen von SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS)Schema and data type mapping in copy activity - Azure Data Factory & Azure SynapseData warehousing in Microsoft Azure - Azure Architecture CenterDiagnostic Data Viewer Overview (Windows 10 and Windows 11) - Windows Privacy

   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>

        

      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:

   • Azure Data Factory
   • Azure Synapse

   

   See Also

   Copy data from and to Salesforce - Azure Data Factory & Azure SynapseCopy and transform data in Dynamics 365 (Microsoft Dataverse) or Dynamics CRM - Azure Data Factory & Azure SynapseCreate an Azure Data Factory using REST API - Azure Data FactoryMicrosoft Surface Data Eraser (Surface) - Surface

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

   

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

   

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

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.

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:

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>" } } }]

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.

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.

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.

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.