Copy data from SharePoint Online List by using Azure Data Factory or Azure Synapse Analytics
APPLIES TO: Azure Data Factory Azure Synapse Analytics
Tip
Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!
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.
This SharePoint Online List connector is supported for the following capabilities:
Supported capabilities | IR |
---|---|
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.
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.
Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:
Search for SharePoint and select the SharePoint Online List connector.
Configure the service details, test the connection, and create the new linked service.
The following sections provide details about properties you can use to define entities that are specific to SharePoint Online List connector.
The following properties are supported for a SharePoint Online List linked service:
Property | Description | Required |
---|---|---|
type | The type property must be set to: SharePointOnlineList. | Yes |
siteUrl | The SharePoint Online site url, e.g. https://contoso.sharepoint.com/sites/siteName . |
Yes |
servicePrincipalId | The Application (client) ID of the application registered in Microsoft Entra ID. | Yes |
servicePrincipalCredentialType | Specify the credential type to use for service principal authentication. Allowed values are ServicePrincipalCert and ServicePrincipalKey . |
No |
For ServicePrincipalCert | ||
servicePrincipalEmbeddedCert | Specify the base64 encoded certificate of your application registered in Microsoft Entra ID, and ensure the certificate content type is PKCS #12. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault. You need to configure the permission settings referring this article. | No |
servicePrincipalEmbeddedCertPassword | Specify the password of your certificate if your certificate is secured with a password. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault. | No |
For ServicePrincipalKey | ||
servicePrincipalKey | The application's key. Mark this field as a SecureString to store it securely, or reference a secret stored in Azure Key Vault. Refer to this section for more details including the permission settings. | No |
tenantId | The tenant ID under which your application resides. | Yes |
connectVia | The Integration Runtime to use to connect to the data store. If not specified, the default Azure Integration Runtime is used. | No |
Note
If you are using service principal key authentication, which is based on Azure ACS (Access Control Services), we recommend switching to the service principal certificate authentication due to the ACS retirement plan.
Example 1: Using service principal key authentication
{
"name": "SharePointOnlineList",
"properties": {
"type": "SharePointOnlineList",
"typeProperties": {
"siteUrl": "<site URL>",
"servicePrincipalId": "<service principal id>",
"servicePrincipalCredentialType": "ServicePrincipalKey",
"servicePrincipalKey": {
"type": "SecureString",
"value": "<service principal key>"
},
"tenantId": "<tenant ID>"
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
Example 2: Using service principal certificate authentication
{
"name": "SharePointOnlineList",
"properties": {
"type": "SharePointOnlineList",
"typeProperties": {
"siteUrl": "<site URL>",
"servicePrincipalId": "<service principal id>",
"servicePrincipalCredentialType": "ServicePrincipalCert",
"servicePrincipalEmbeddedCert": {
"type": "SecureString",
"value": "<base64 encoded string of (.pfx) certificate data>"
},
"servicePrincipalEmbeddedCertPassword": {
"type": "SecureString",
"value": "<password of your certificate>"
},
"tenantId": "<tenant ID>"
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
The SharePoint List Online connector uses service principal authentication to connect to SharePoint. Follow these steps to set it up:
Register an application with the Microsoft identity platform. To learn how, see Quickstart: Register an application with the Microsoft identity platform. Make note of these values, which you use to define the linked service:
- Application ID
- Application key
- Tenant ID
Grant SharePoint Online site permission to your registered application by following the steps below. To do this, you need a site admin role.
Open your SharePoint Online site link. For example, the URL in the format
https://<your-site-url>/_layouts/15/appinv.aspx
where the placeholder<your-site-url>
is your site.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
In the context of configuring the SharePoint connector, the "App Domain" and "Redirect URL" refer to the SharePoint app that you have registered in Microsoft Entra ID 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.
Click "Trust It" for this app.
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.
Property | Description | Required |
---|---|---|
type | The type property of the dataset must be set to SharePointOnlineLResource. | Yes |
listName | The name of the SharePoint Online List. Note that the apostrophe (') is not allowed in file names. | Yes |
Example
{
"name": "SharePointOnlineListDataset",
"properties":
{
"type": "SharePointOnlineListResource",
"linkedServiceName": {
"referenceName": "<SharePoint Online List linked service name>",
"type": "LinkedServiceReference"
},
"typeProperties":
{
"listName": "<name of the list>"
}
}
}
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:
Property | Description | Required |
---|---|---|
type | The type property of the Copy Activity source must be set to SharePointOnlineListSource. | Yes |
query | Custom OData query options for filtering data. Example: "$top=10&$select=Title,Number" . |
No |
httpRequestTimeout | The 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.
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 type | OData data type | Interim data type |
---|---|---|
Single line of text | Edm.String | String |
Multiple lines of text | Edm.String | String |
Choice (menu to choose from) | Edm.String | String |
Number (1, 1.0, 100) | Edm.Double | Double |
Currency ($, ¥, €) | Edm.Double | Double |
Date and Time | Edm.DateTime | DateTime |
Lookup (information already on this site) | Edm.Int32 | Int32 |
Yes/No (check box) | Edm.Boolean | Boolean |
Person or Group | Edm.Int32 | Int32 |
Hyperlink or Picture | Edm.String | String |
Calculated (calculation based on other columns) | Edm.String / Edm.Double / Edm.DateTime / Edm.Boolean | String / Double / DateTime / Boolean |
Attachment | Not supported | |
Task Outcome | Not supported | |
External Data | Not supported | |
Managed Metadata | Not 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.
Follow the Grant permission for using service principal key section to create Microsoft Entra application and grant permission to SharePoint Online.
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.
- URL:
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 ashttps://[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)
- Base URL:
- 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 for any supported sink destination.
- HTTP linked service:
Note
Even if a Microsoft Entra application has FullControl
permissions on SharePoint Online, you can't copy files from document libraries with IRM enabled.
To learn details about the properties, check Lookup activity.
For a list of data stores that Copy Activity supports as sources and sinks, see Supported data stores and formats.