Automate OneDrive Excel loads to SQL Server with Power Automate

A Power Automate checklist for ingesting quarterly Excel files from OneDrive into SQL Server, with validation, upserts, run logging, and safe reruns.

Workflow Automation Data integration

Oct 9, 2026

Automate OneDrive Excel loads to SQL Server with Power Automate

✅

Quick answer: Use a OneDrive “drop” folder + a Power Automate flow that (1) validates the quarterly Excel file, (2) transforms it into a load-ready shape, (3) upserts into SQL Server, and (4) moves the file into a /processed folder with run logs so reruns don’t create duplicates.

Power Automate OneDrive Excel to SQL Server ingestion starts with a clean, predictable spreadsheet. Photo by Gorilla ROI Data Connector on Unsplash

What this checklist covers (and when it’s the right pattern)

Use this pattern when:

New Excel files land in a known OneDrive folder (or a SharePoint library) on a predictable cadence (quarterly, monthly, weekly).
Your downstream system of record is SQL Server.
You need a repeatable “drop → ingest → archive” pipeline with clear auditability.

This is not ideal when:

You need heavy transformations (do those upstream in a proper ETL tool), or
You need to bulk-load very large files quickly (a bulk import tool may be a better fit than row-by-row inserts).

Architecture at a glance

OneDrive folder convention

/incoming/<year>/<quarter>/ (where new files are dropped)
/processed/<year>/<quarter>/ (where you move files after a successful load)
/error/<year>/<quarter>/ (optional: quarantine folder)

Power Automate trigger

OneDrive for Business “When a file is created (properties only)” on the /incoming folder.

Transform + validate

Normalize column names, validate required columns, and convert wide → long if needed.

Load into SQL Server

Prefer an upsert approach (MERGE or stored procedure) to keep reruns safe.

Logging + observability

Log run id, file name, hash, quarter, row counts, and error messages.

Move file to /processed

Archive the exact file that was loaded so future reruns can be controlled.

Prereqs (do these before building the flow)

1) Permissions + access prerequisites

A dedicated Power Automate service account with:

Access to the OneDrive folder
Access to SQL Server (network path, firewall/VPN, and SQL auth strategy confirmed)

A recovery / MFA strategy that does not break when someone else on the team needs to run or edit the flow.

2) Agree on the “quarterly file contract”

Before you automate, get agreement on:

Expected file naming convention (include year + quarter)
Whether each quarter arrives as one file or several
Required tabs/sheets and required columns
What “ready to ingest” means (e.g., any consolidation macro has finished running)

3) SQL Server target design decisions

Decide up front:

Do you load into a staging table first, then transform into final tables?
What is the unique key for idempotency (Quarter + Asset ID, etc.)?
Do you need a “load batch” table to track each run?

Step-by-step checklist: Power Automate OneDrive Excel to SQL Server ingestion

Step 1: Create the OneDrive folder structure

Create /incoming and /processed under the project folder in OneDrive.

Confirm who can drop files into /incoming (and who should not be able to see /processed).

Write down the file naming rules (examples help).

Step 2: Create the Power Automate trigger

Trigger: OneDrive for Business → When a file is created (properties only), pointed at /incoming. Files moved within OneDrive do not fire it, and the full “When a file is created” trigger skips files over 50 MB (per Microsoft connector docs, as of October 2026).

Immediately fetch file metadata (name, path, created time, size).

Guardrail: if the file is still being written (size changing), delay and re-check before processing.

Step 3: Validate the file before you touch SQL

Confirm the file name matches the quarter pattern (e.g., 2026-Q2-*.xlsx).

Confirm it’s the expected file type (.xlsx) and not a temporary file.

Extract quarter/year from the name or path.

Validate required sheets / tables exist.

Validate required columns exist and are non-empty where required.

If validation fails:

Move the file to /error (or leave in place but tag it) and send an alert.

Step 4: Transform the data into a load-ready shape

Typical transformation steps:

Normalize column names (trim, consistent casing, remove special characters).

Convert “wide” financial models into “long” rows (if needed):

Example: Metric, Period, Value instead of Jan, Feb, Mar columns.

Standardize types (dates, decimals, currency codes).

Create a deterministic row key (see idempotency below).

Step 5: Load into SQL Server (prefer upsert, not insert)

Avoid “insert-only” loads unless you can guarantee the flow will never rerun.

Options

Stored procedure (recommended):

Flow calls one proc with the quarter + payload, and SQL handles validation + upsert.

MERGE / upsert pattern:

Use a unique key to update existing rows and insert new ones.

Performance note

For larger files, reduce per-row roundtrips:

Batch rows where possible, or
Load to a staging table and let SQL do set-based operations.

Step 6: Make reruns safe (idempotency checklist)

Quarterly pipelines will rerun. People will upload the same file twice. A flow will fail mid-run. Plan for it.

Create a LoadRuns table with: RunId, Quarter, FileName, FileHash, StartedAt, EndedAt, Status, RowCount, Error.

Compute a file hash (or another stable fingerprint) and store it in LoadRuns.

If a file hash has already been successfully processed for that quarter:

Skip processing, or

Process but only upsert (no net change).

Ensure SQL tables enforce uniqueness via a unique index or constraint where appropriate.

Step 7: Move the file to /processed (and keep the audit trail)

On success, move the file to /processed/<year>/<quarter>/.

Include the RunId in the destination file name (or store the mapping in SQL).

On failure, do not move to /processed.

Step 8: Add alerts + run visibility

Pick at least one:

Email or Microsoft Teams notification on failure

Dashboard table in SQL or a lightweight log in a spreadsheet

Weekly digest of loads (what ran, what failed, what changed)

Common failure modes (and the fix)

Trigger fires too early (file still uploading): add a delay + size check loop.
Duplicates after reruns: add unique keys + upsert, and track file hashes.
Missing columns / sheet renamed: validate schema before load, fail fast.
Permissions drift: keep access checks and alert when the service account loses access.

Get help building your ingestion pipeline

We build Power Automate pipelines like this for teams that are done copying quarterly Excel files into SQL Server by hand. If you want a flow that validates files, upserts cleanly, and survives reruns without duplicates, book a free discovery call with a Connex consultant.

Fix Smokeball→Outlook contact dedup and updates Notion Workers: what they are + how to build and deploy one