Sharepoint List extract error from ADF

Question

Sharepoint List extract error from ADF

ABEL ANTON RODRIGUEZ 0

Dear Support,

I am reaching out regarding an issue we are encountering while extracting data from several SharePoint lists using Azure Data Factory (ADF).

We have been able to successfully extract 10 out of 14 lists without any problems. However, when attempting to extract the remaining four, we consistently receive the following error:

An error occurred while sending the request.

The underlying connection was closed: An unexpected error occurred on a receive.

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host.

To help us identify and resolve the issue, we would appreciate your support with the following questions:

What typically causes this type of connection error when using the SharePoint Online connector in Azure Data Factory?

Are there any known limitations in terms of list size, structure, number of columns, or data types that might cause some lists to fail while others succeed?

Could throttling or timeout policies in SharePoint Online be affecting the connection for certain lists?

Is there a way to increase the reliability or resilience of the connection (e.g., retry policies, adjusting timeout settings, batch size)?

Are there diagnostic steps or logs we can collect to help isolate whether the failure originates in ADF, SharePoint Online, or the network layer?

Finally, what would be the recommended steps to resolve this issue and ensure extraction works consistently for all lists?

We are happy to provide additional technical details or configuration snapshots if needed.

Looking forward to your guidance to help resolve this issue.

Best regards,

Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-06-20T05:39:03.6366667+00:00

@ABEL ANTON RODRIGUEZ Following up to see if the below answer was helpful. 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.

1 answer

Your answer

Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-06-20T05:39:03.6366667+00:00

@ABEL ANTON RODRIGUEZ Following up to see if the below answer was helpful. 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.

Answer 1

Venkat Reddy Navari 2,885 Microsoft External Staff Moderator

Hi @ABEL ANTON RODRIGUEZ Thanks for outlining the issue so clearly that helps a lot. Let’s go through the possible causes and what you can do to troubleshoot and improve reliability when pulling from SharePoint lists using Azure Data Factory (ADF).

What could be causing this error?

This error typically happens when SharePoint Online closes the connection unexpectedly and that can be due to:

Throttling by SharePoint when too many or too large requests are made.
Large or complex list structure, which can slow down responses.
Timeouts or dropped connections, especially when payloads are heavy (e.g., attachments, lookup fields, or very large item sets).

Throttling is a common one SharePoint is pretty strict about protecting service health, especially with high-volume automated requests.

Docs reference: How to avoid getting throttled in SharePoint Online

Are there known limits with the ADF SharePoint Online connector?

Yes, there are a few things worth keeping in mind:

Lists with more than 5000 items may require paging (ADF usually handles this, but not always cleanly).
Some column types, like multi-select choice fields or complex lookups, don’t always behave well with the connector.
Lists with a large number of columns (or system/hidden fields) can hit schema inference issues.
Here’s the official connector doc that outlines some of these: SharePoint Online list connector in ADF

What can help improve reliability?

Enable retry logic in your ADF copy activity something like 3 retries with a 30-second interval.
Reduce parallelism if you're querying multiple lists at once. Hitting them all at once can trigger throttling.
Use simpler views limit the number of columns and avoid complex filters. This lightens the payload.
Paginate or filter large lists break up extraction by created/modified date range or ID.
If you're hitting this consistently with specific lists, try querying them via Graph API or Postman to see if the issue reproduces outside ADF.

Any logs or diagnostics to check?

Yes, a few useful places:

ADF Activity Run output – especially the error details and ErrorCode.
Azure Monitor or Log Analytics, if you’ve connected your ADF to it — can give better visibility into pipeline failures.
Also, check the headers returned by SharePoint (if accessible) — they might show Retry-After or related hints when throttling is involved.
More on monitoring ADF: Monitor Azure Data Factory pipelines

Steps suggest moving forward:

Narrow down the issue to those 4 lists – are they larger or structurallydifferent?
Create custom views on those lists that simplify the structure — fewer columns, no lookups or rich text if possible.
Update the copy activity with retries and reduce concurrency for those extractions.
If the issue still happens, try querying one of the failing lists via Graph API or Postman this helps confirm if it’s a SharePoint-side issue.
As a fallback, consider using an Azure Function or Logic App to pull data from the list using Graph, and then push it to ADF.

Hope this helps. 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.

Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-06-18T09:40:14.3766667+00:00

Hi @ABEL ANTON RODRIGUEZ Following up to see if the above answer was helpful. 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.

ABEL ANTON RODRIGUEZ 0

Hi Support team,

Current status

We have 14 SharePoint-Online lists under /sites/MantenimientoGeneralPro.
Using the SharePoint Online List connector in Azure Data Factory we already copy 10 lists successfully; some of them hold 10 k–28 k items and include calculated, choice, and person/lookup columns—all load fine into Blob → Snowflake.
4 lists still fail:
- 3 lists contain fewer than 1 000 items each.
- 1 list contains ≈ 17 000 items.

For any of those four lists—even with

  $top=10&$select=ID,Title

in Preview or in a Copy activity—we consistently get

  An error occurred while sending the request.
  The underlying connection was closed: An unexpected error occurred on a receive.
  Unable to read data from the transport connection:
     An existing connection was forcibly closed by the remote host.

What we have verified

Check	Result
Integration Runtime	Azure-IR in Managed VNet ⇒ TLS 1.2/cipher-suites are current.
Integration Runtime	Azure-IR in Managed VNet ⇒ TLS 1.2/cipher-suites are current.
Credentials	Service Principal is trusted via AppInv with site-collection Read; same SP reads the other 10 lists.
Timeout	`httpRequestTimeout = "00:10:00"` in `SharePointOnlineListSource` → no change.
Payload reduction	`$top=10` & `$select=ID` → still fails before Preview returns data.
Complex columns	Calculated / attachments / lookups also exist in the lists that work.
Thresholds	None of the failing lists hit the 5 000-item view threshold.

Is there any way to obtain more detailed information about the error or to understand why the connection is being closed abruptly when accessing these specific lists?

Is there anything else we can try or configure—either in the connector settings or elsewhere—to troubleshoot or work around this issue?

Any guidance would be highly appreciated. Thank you!

Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-06-18T15:19:08.2933333+00:00
Hi @ABEL ANTON RODRIGUEZ That error "An existing connection was forcibly closed by the remote host" — usually means that SharePoint Online is terminating the connection unexpectedly. It can happen due to a few reasons:

Intermittent throttling by SharePoint for specific lists

A misbehaving backend node or edge server on the SPO side

TLS/cipher suite handshake issues (though you’ve already confirmed those are fine)

Since this is happening only for 4 lists, and consistently, there may be something specific about the way those lists are hosted or indexed on the SPO backend.

Here are a few troubleshooting steps:

Graph API or Postman test: Try calling one of the failing lists using Microsoft Graph or Postman — this helps check if the issue happens outside of ADF too. For example:

GET https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?$top=10

If that fails too, it's a good indicator that the problem lies with SharePoint, not ADF.

Try with a self-hosted IR: Even though you’re using Azure IR inside a Managed VNet (which is a great practice), it’s worth trying the same copy activity from a self-hosted IR. This helps isolate whether there’s something in the network path or routing from the Managed VNet that’s causing issues.

Turn on ADF diagnostics/logging: If you haven’t already, enable diagnostic logs in ADF and route them to Log Analytics. Look specifically at ActivityRuns for any additional clues in the error message. Sometimes, the error shows up in more detail there than in the UI.

Adjust ADF retries and parallelism: Even for light queries like $top=10, try tweaking these:

Retry: 3–5 attempts

Retry interval: 30–60 seconds

Degree of parallelism: set to 1 (especially if you’re querying multiple lists in parallel)

Try simplifying the list view further: If possible, create a view on one of the failing lists that only includes a couple of basic columns (like ID and Title), then use that in your ADF dataset. I know you've already tried $select, but sometimes simplifying the default view in SharePoint itself helps bypass rendering or parsing issues.

Run headers check: If you're able to test via Postman or curl, look at the response headers SharePoint may return something like Retry-After or even more specific failure hints when throttling is involved.

Hope this helps. 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.
Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-06-19T09:43:50.24+00:00

@ABEL ANTON RODRIGUEZ Following up to see if the above answer was helpful. 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.
ABEL ANTON RODRIGUEZ 0 Reputation points

2025-06-19T10:00:24.63+00:00
Hi @Venkat Reddy Navari

Thank you again for the previous suggestions.

Following your recommendations, I wanted to share that we've already tested the retry logic and timeout settings in detail:

We've configured retry attempts to 3 and 5, respectively, and tried different retry intervals: 30, 60, and even 120 seconds.

We also ensured that lists were processed one by one, with degree of parallelism = 1.

We increased the timeout value of the Copy activity significantly to rule out delays — however, we never hit a timeout. The connection always fails before reaching the timeout threshold, with the same error:
The underlying connection was closed: An unexpected error occurred on a receive.

Despite all these adjustments, the issue persists exactly the same way for the same 4 lists.

At this point, could you suggest any alternative methods for extracting SharePoint Online lists into Azure Data Factory or any other supported connector/workaround you recommend.

Appreciate your help — we’re eager to find a working solution.

Best regards
Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-06-19T12:56:09.3633333+00:00
@ABEL ANTON RODRIGUEZ Thanks for the detailed breakdown, it's clear you’ve already ruled out all the standard ADF-side tuning levers (retry logic, timeout settings, parallelism, and view simplification). Given that even a minimal $top=10&$select=ID,Title request fails, this strongly points to a low-level network or SharePoint Online (SPO) backend-specific issue, likely tied to how those particular lists are hosted or served internally.

Since this behavior is consistent and list-specific, and you've already confirmed TLS compatibility and SPN permissions, the most effective next step would be to bypass the SharePoint connector and use Microsoft Graph API with Azure Function as an alternative extraction method.

Step-by-step Solution Using Graph API + ADF (via Azure Function)

1. Switch from ADF SharePoint Connector to Microsoft Graph API
The SharePoint Online connector in ADF is known to hit reliability issues with some complex or backend-throttled lists. Microsoft Graph offers a more direct and reliable path. Example call:

GET https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?$top=500&expand=fields

2. Build an Azure Function (or Logic App) to Query the List
Use a timer-triggered or HTTP-triggered Azure Function (C#, Python, or JavaScript) that:

Authenticates using the same Service Principal

Pulls list items using Microsoft Graph with paging and retry logic

Writes results to Azure Blob Storage (as JSON or CSV)

You can also handle schema normalization here (e.g., flattening person, lookup, or multi-choice fields) before ingestion into Snowflake.

3. Ingest into Snowflake via ADF
Once the Azure Function drops data into Blob, ADF can resume its role — use a standard Copy Activity to move data from Blob into Snowflake. This cleanly separates SPO extraction from downstream ingestion and avoids connector limitations entirely.

Hope this helps. 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.

Share via

Sharepoint List extract error from ADF

1 answer

Your answer