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 copy files from SharePoint to Azure Blob Storage using ADF? ›

To copy files from SharePoint to Azure Blob Storage using Azure Logic Apps in Azure Data Factory, you can follow these steps:
  1. Navigate to the Azure portal and sign in with your Azure account.
  2. In the search bar, type "Logic Apps" and select the "Logic Apps" service.
  3. Click the "Add" button to create a new logic app.
Jun 21, 2022

How do I copy a SharePoint list and data from one site to another? ›

How to migrate list from one site collection to another site collection?
  1. Step 1: Export the list. Login to SharePoint and go to List Settings. ...
  2. Step 2: Export list to another site collection. ...
  3. Step 3: Uploading the list to a new site collection.

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 Connect to SharePoint from Synapse? ›

Azure Synapse. Search for SharePoint and select the SharePoint Online List connector. Configure the service details, test the connection, and create the new linked service.

How do I extract data from SharePoint? ›

Navigate to the SharePoint site that contains the list. , select Site contents or View All Site Content, and then in the appropriate list section, select the name of the list. Select List, and then select Export to Excel.

How do I easily copy files from SharePoint to local folder? ›

Copy
  1. Select the items that you want to copy, and then select Copy to. Or, for the item, select More. ...
  2. Under Choose a destination, select the location where you want a copy of the files or folders. ...
  3. In the text box, enter the name of the new folder.
  4. Select the checkmark, and then select Copy here.

How do I copy data from Azure data Factory? ›

Use the copy data tool to copy data
  1. Step 1: Start the copy data Tool. On the home page of Azure Data Factory, select the Ingest tile to start the Copy Data tool. ...
  2. Step 2: Complete source configuration. ...
  3. Step 3: Complete destination configuration. ...
  4. Step 4: Review all settings and deployment. ...
  5. Step 5: Monitor the running results.
Oct 25, 2022

How do I copy a list of files in ADF? ›

Copy a set of files using ADF
  1. using lookup to retrieve the filenames from a json file- then feeding it to a for each iterator.
  2. using metadata to get file names from source folder and then adding if condition inside a for each to copy the files.

How do I copy data from a SharePoint list? ›

You can open the grid view of the list using the Edit in grid view (1) button or export all rows of the SharePoint list to the Excel file by selecting Export to Excel (2). In the Grid View, you can select all rows to copy them. This is the same operation you can perform in exported Excel file.

How do I copy a SharePoint data list? ›

Open your excel sheet and select the data to be copied. Then click on the Format as a Table option. 2. While still selecting the data table, choose an Export option from the ribbon and select the 'Export table to SharePoint List' option.

How do I copy an item from a SharePoint list? ›

To clone an item, simply:
  1. Select the item to copy, click on "Automate", wait a few seconds, our flow will appear and just click.
  2. On the right side, a permission request will appear to connect to Sharepoint and then to execute the flow.
Jul 3, 2020

What are the limitations of Azure Synapse database? ›

Limit is 8000 for char data types, 4000 for nvarchar, or 2 GB for MAX data types. The number of bytes per row is calculated in the same manner as it is for SQL Server with page compression. Like SQL Server, row-overflow storage is supported, which enables variable length columns to be pushed off-row.

Does Synapse include data Factory? ›

Azure Data Factory and its equivalent pipelines feature within Azure Synapse itself provide a fully managed cloud-based data integration service. You can use the service to populate an Azure Synapse Analytics with data from your existing system and save time when building your analytics solutions.

Why use Synapse over ADF? ›

Unless you are looking to use legacy features, use CI/CD that ADF have, or general orchestration, it's probably better to focus on Azure Synapse. It reduces deployed resources, permissions, and general cost of maintaining multiple resources.

How do I send data to Azure Synapse? ›

Prerequisites for receiving data into Azure Synapse Analytics (workspace) SQL pool
  1. In the Azure portal, navigate to Synapse workspace. Select Firewalls from left navigation.
  2. Select ON for Allow Azure services and resources to access this workspace.
  3. Select +Add client IP. ...
  4. Select Save.
Oct 27, 2022

Does Azure Synapse support cross database query? ›

Azure Synapse Analytics also doesn't support cross database query.

How do I connect to Azure Synapse database? ›

To connect to Synapse SQL using dedicated SQL pool, follow these steps:
  1. Open SQL Server Management Studio (SSMS).
  2. In the Connect to Server dialog box, fill in the fields, and then select Connect: Server name: Enter the server name previously identified. ...
  3. Expand your Azure SQL Server in Object Explorer.
Feb 18, 2022

How do I export files from SharePoint online? ›

How to Download All Files in SharePoint Site?
  1. Navigate to your SharePoint On-premises or SharePoint Online library.
  2. Click on “View in File Explorer” from the Views Drop down in SharePoint Online. ...
  3. From the Explore view, you can download all documents or all attachments from any list or library to your local machine.

How do I move data from SharePoint to SQL? ›

Common Ways to Import Data from SharePoint to SQL Server
  1. Directly access the SharePoint database to export SharePoint data. ...
  2. Export SharePoint list into SQL server using CSOM and PowerShell script. ...
  3. SQL Server Integration Services (SSIS) and the SharePoint List Adapter component for exporting SharePoint data.
Jul 8, 2022

How do I export a SharePoint list to CSV? ›

To export the SharePoint Online list to a CSV File, Simply navigate to the list and click on “Export to CSV” from the “Export” menu, and SharePoint exports the list data to a CSV file. It's that simple!

Is there a way to mass download files from SharePoint? ›

How to download multiple files from a SharePoint link
  1. To start downloading files, find your file folder in SharePoint.
  2. Select the files you want to download by clicking the circles on the left side of the file name.
  3. Once you have the files selected, click the “Download” button in the command bar.
Oct 3, 2022

Can you drag and drop files from SharePoint? ›

You can drag and drop documents from one folder to another, one library to another and from windows explorer to the library. Open your windows explorer window and restore the size of the window so it can be viewed on the same screen with your SharePoint window.

Is there a way to sync SharePoint files locally? ›

From the menu that opens, select SharePoint or Teams, and then select the site with the files you want to sync. Select Documents or navigate to the subfolder you want to sync. Select Sync. (You only need to do this once on a computer to set up syncing on that computer.

How do I import data into Azure Data Factory? ›

Select on the Azure SQL Database tile and select continue. In the SQL DB configuration pane, enter 'SQLDB' as your linked service name. Enter in your credentials to allow data factory to connect to your database. If you're using SQL authentication, enter in the server name, the database, your user name and password.

How do I move SharePoint from premise to Azure? ›

The following steps relate to SharePoint.
  1. Create a VM in the Azure portal and mount the Azure file share on it.
  2. On the Azure VM, install SPMT.
  3. Using the Azure file share as the SOURCE, run SPMT.
  4. Complete the final steps of SPMT.
  5. Verify and confirm your data.
May 25, 2022

How do I pull data from SharePoint to SQL? ›

4) Export to Excel first and then load to SQL Server
  1. Actions > Export to SpreadSheet.
  2. Save the Excel file.
  3. Open your SQL database with SQL Management Studio.
  4. Right click on your database > Tasks > Import Data.
  5. Select your Excel file as the source, and specific table as destination.
  6. Fill all other needed options.

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.

How do I load data into Azure synapse? ›

Load data into Azure Synapse Analytics
  1. Select + New connection.
  2. Select Azure SQL Database from the gallery, and select Continue. ...
  3. In the New connection (Azure SQL Database) page, select your server name and DB name from the dropdown list, and specify the username and password.
Aug 16, 2022

How do I transfer files from Azure Data factory? ›

Go to the Move files template. Select existing connection or create a New connection to your source file store where you want to move files from. Be aware that DataSource_Folder and DataSource_File are reference to the same connection of your source file store.

What connects an Azure Data factory activity to a dataset? ›

The Azure Storage and Azure SQL Database linked services contain connection strings that Data Factory uses at runtime to connect to your Azure Storage and Azure SQL Database, respectively. The Azure Blob dataset specifies the blob container and blob folder that contains the input blobs in your Blob storage.

Videos

1. Azure Data Factory | Copy multiple tables in Bulk with Lookup & ForEach
(Adam Marczak - Azure for Everyone)
2. 32. Copy data from REST API which sends response in Pages using Azure data factory
(WafaStudies)
3. Copy Documents from SharePoint to Azure Storage using Power Automate
(KeaPoint Tech Tips)
4. Power Automate: Transfer records from SharePoint list to Azure SQL database
(Mariano Gomez)
5. Copy Data From An HTTP Endpoint By Using Azure Data Factory - ADF Tutorial 2021
(TechBrothersIT)
6. 20. Get Latest File from Folder and Process it in Azure Data Factory
(WafaStudies)
Top Articles
Latest Posts
Article information

Author: Merrill Bechtelar CPA

Last Updated: 02/06/2023

Views: 6445

Rating: 5 / 5 (50 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Merrill Bechtelar CPA

Birthday: 1996-05-19

Address: Apt. 114 873 White Lodge, Libbyfurt, CA 93006

Phone: +5983010455207

Job: Legacy Representative

Hobby: Blacksmithing, Urban exploration, Sudoku, Slacklining, Creative writing, Community, Letterboxing

Introduction: My name is Merrill Bechtelar CPA, I am a clean, agreeable, glorious, magnificent, witty, enchanting, comfortable person who loves writing and wants to share my knowledge and understanding with you.