POWER AUTOMATE — PROJECT 4🟡 INTERMEDIATE
CRM-to-ERP Nightly Sync — premium connectors, the on-premises data gateway, and authenticated HTTP integration.
Dataverse
Account records
→
On-Prem Gateway
single point of failure ⚠
→
SQL Server ERP
upsert target
→
Partner API
HTTP + Azure AD
⚠ Recommended for production: gateway cluster, not a single machine
Flow structure
Dataverse: List rows (paginated)
Account, modified last 24h
SQL Server: Get row by ID
via gateway — check exists
Update row / Insert row
manual upsert pattern
HTTP (Premium): POST /confirm
Partner API
✔ Azure AD client credentials
SyncLog (Dataverse) — durable audit trail
ACC-2291 → ERP✔ Synced
ACC-2305 → ERP✔ Synced
ACC-2312 → Partner API✕ HTTP 200, body: error
Row 3 shows why checking the response body, not just the status code, matters — a 200 doesn't always mean the business operation succeeded.
What this shows: the manual upsert pattern against SQL Server, Azure AD authentication (not a static token) on the outbound HTTP call, and an independent Dataverse SyncLog that catches a "200 but actually failed" case the platform's own success/fail status would have missed.