Replacing Data Factory pipelines and scheduled flows that read SharePoint

Last updated 23 October 2026

A Data Factory pipeline or Power Automate flow was built to get SharePoint list data into a database, and it is slow, brittle against schema changes, and expensive to maintain.

A Data Factory copy activity or a Power Automate flow that moves a SharePoint list into the reporting database takes an afternoon to build. Two years later the month-end close depends on it, the person who built it has moved on, and it fails often enough that someone checks it by hand every morning.

How a quick copy becomes a maintenance job

It grows one fix at a time. Lookups needed resolving, so a mapping step appeared. The list passed 5,000 items and paging had to work around the list view threshold. Graph started throttling under load, so retry logic went in. Our post on what breaks past 5,000 items describes that stage, and for a flow, the post on Power Automate Get items past 5,000, 100,000 and beyond covers the pagination caps and the deleted items a copy flow never sees. Then a column was added in SharePoint, the mapping step broke, and the gap only showed when a report came out wrong. Each fix was reasonable. Together they make a pipeline that is slow, brittle against schema changes, and understood only by people who have left.

Keep the pipeline, replace one step

SPSync takes over the SharePoint-to-database leg and nothing else. It handles the 5,000-item threshold and Graph throttling itself, honouring Retry-After and metering resource use. Schema changes stop being incidents. A new column appears in the destination table on the next sync, and a removed column stays where it is, so no mapping step breaks. On SQL Server, PostgreSQL, MySQL, ClickHouse, Oracle, SQLite and DuckDB, a retyped column moves into a _v2 column, where a copy activity’s type conversion would have mangled it.

Transformations, orchestration and the other sources stay in Data Factory or Power Automate. They read a stable table where they used to call SharePoint.

What downstream steps read

Each list becomes a table with its lookup, person, choice and calculated columns. Lookups become key columns and multi-choice columns become child tables, so the steps after it join on keys and never parse a delimited list again. Multi-value lookups are still being verified against a live tenant. Deleted items keep their row with sp_deleted_at set, which matters to any step that needs to know a record was removed. When the rest of the pipeline expects a flatter shape, optional reporting views add person names to each row.

A side-by-side cutover

The safest replacement runs alongside the old one for a while.

1. Sign in and pick the sites and lists the pipeline copies today.
2. Point SPSync at the database the pipeline already writes to, or a new one.
3. Run the initial full load and compare it with the pipeline’s last output.
4. Set a schedule that matches the pipeline’s cadence or beats it.
5. Repoint the downstream steps at the replicated tables and retire the step that read SharePoint.

SPSync never writes to SharePoint, so running both side by side puts nothing on the source lists at risk. The destination pages list which destinations are shipped today.

Choosing where SPSync runs

Trial the replacement against one site from the desktop app before production. For the production job, SPSync in your Azure is the closest match for a pipeline that ran unattended, and the command line runs from the scheduler or CI pipeline the old job ran from, with a YAML config that reads well in review.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name replace_adf_and_flows --prefix replace_adf_and_flows --destination-kind mssql --connection-string-env SPSYNC_DB_CONNECTION --dataset-name replace_adf_and_flows --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: replace_adf_and_flows
prefix: replace_adf_and_flows
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: mssql
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: replace_adf_and_flows
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist