How to copy data from a large SharePoint Online list in Azure Data Factory (ADF)?

asnth 0

I am having trouble copying data from a large SharePoint Online list that has many records and columns in Azure Data Factory. I am unable to preview or copy the data, and I'm not sure if the issue is with the size of the list or the column types.

I've attempted to use the Query option to filter by modified date or select the Id column alone, but the operation times out. My Request timeout is set for 1 hour, but the data preview times out after a minute or so. I've successfully previewed and copied data with/without these queries from other lists on the site. Can anyone help? Thanks.

NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-07-29T12:13:16.63+00:00

Hi @asnth
Just checking in to see if the below answer provided by @Pinaki Ghatak helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-07-31T10:14:51.81+00:00

Hi @asnth
According to your description, this problem only occurs in specific SharePoint list. Please try to do a troubleshooting, check if the SharePoint Online data type is not supported with the OData data type.
For more information, please refer to this link:
https://learn.microsoft.com/en-us/answers/questions/1071895/adf-copydata-to-sharepoint-online-event-list-retur

Hope this helps. Please let us know if you have any further questions.
asnth 0 Reputation points

2024-07-31T11:24:07.4066667+00:00

@NIKHILA NETHIKUNTA how would I check if the data type was not one of the listed data types? Also, I still get the error when I am trying to return the Id column alone, which is supported.
NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-08-01T16:18:24.7466667+00:00

Hi @asnth
I agree that this issue looks strange, and I wasn't able to reproduce this issue. If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.
Thanks
NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-08-05T08:45:26.18+00:00

Hi @asnth
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
NIKHILA NETHIKUNTA 3,265 Reputation points Microsoft Vendor

2024-08-06T06:34:26.4633333+00:00

Hi @asnth
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
asnth 0 Reputation points

2024-08-06T10:36:52.1466667+00:00

@NIKHILA NETHIKUNTA I have not yet found a solution.

2 answers

Pinaki Ghatak 4,610 Reputation points Microsoft Employee

2024-07-26T10:59:48.0866667+00:00
Hello @asnth

There could be several reasons why you are unable to preview or copy the data, such as the size of the list, the column types, or the query you are using.

To troubleshoot this issue, I recommend the following steps:

Check the size of the list: If the list is very large, it may take a long time to preview or copy the data. You can try to limit the number of records returned by using the Query option to filter by modified date or select the Id column alone.

Check the column types: If the list contains columns with complex data types, such as lookup columns or managed metadata columns, it may cause issues with the data preview or copy operation. You can try to exclude these columns from the copy operation or use a different data type mapping.

Check the Request timeout: If the data preview times out after a minute or so, it may be due to the Request timeout setting. You can try to increase the Request timeout to a higher value, such as 2 hours, to see if it resolves the issue.

Check the network connectivity: If the issue persists, it may be due to network connectivity issues between Azure Data Factory and SharePoint Online. You can try to test the network connectivity by using a different tool, such as PowerShell or SharePoint Online Management Shell, to see if you can access the list data. If none of these steps resolve the issue, you may need to contact Microsoft Support for further assistance. I hope this helps! Let me know if you have any other questions.

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.
Please sign in to rate this answer.
asnth 0 Reputation points

2024-07-29T20:29:15.67+00:00

Hi @Pinaki Ghatak ,

Thanks for your reply. I have tried all of your suggestions but I am still experiencing the issue.

The list contains ~30k rows of data, and there are indeed several complex columns. I have tried to select the Id column and return 1 row of data ($select=Id&$top=1) and I receive an InternalServiceError. When the same query is ran on another list on the site, I can return data. In addition, I increased the request timeout to 2 hours.

There is no network connectivity issues as I can connect and copy from other lists on the site. In addition, I have used the SharePoint REST API to construct the URL using the same filters and I am able to view the data in my browser (https://<name>.sharepoint.com/sites/<sitename>/_api/web/lists/getbytitle('<LISTNAME>')/items?$top=1&$select=Id).

Kindly see the error message from ADF below:

Failure happened on 'Source' side. ErrorCode=ODataRequestNotSucceeded,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Requesting response from path LISTNAME and query $select=Id&$top=1 failed with status code InternalServerError and message <?xml version="1.0" encoding="utf-8" standalone="yes"?> <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code></code> <message xml:lang="en-US">An error occurred while processing this request.</message> </error>.,Source=Microsoft.DataTransfer.Runtime.ODataConnector,'
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

How to copy data from a large SharePoint Online list in Azure Data Factory (ADF)?

2 answers

Your answer