Sage 200 & Evolution Integration Patterns for the Power Platform

Why Sage integration is where most Power Platform projects get stuck

Sage 200 Evolution is the accounting backbone for a large share of South African mid-market businesses — and the moment you try to build a Power App, a Power Automate flow, or a Power BI report against it, you hit the same set of questions: do I go directly at the SQL database, use the Sage Web API, stage through Dataverse, or export to a data lake? Get this wrong and you either break upgrade compatibility, blow past your support contract, or build a dashboard that goes stale the day it ships.

There are four integration patterns that actually work in production. Each has a specific job. Most healthy Sage-integrated Power Platform environments use two or three of them together.

Pattern 1 — Direct SQL read against the Sage database

Sage 200 and Sage Evolution both run on Microsoft SQL Server, and the schema is documented (well, documented-ish). The fastest way to get data out of Sage for reporting or automation is a read-only SQL user querying the tables and views directly.

Use it for: Power BI dashboards, Power Automate flows that need to look up a customer or supplier, and any read-only integration where near-real-time is enough (5–15 minute refresh).

What to actually do:

• Create a dedicated SQL login (e.g. svc_reporting) with db_datareader on the Sage company database and nothing else. No writes, no schema changes, no stored procedure execute.
• Build a small layer of SQL views in a separate schema (rpt.CustomerLedger, rpt.OpenInvoices) that translate Sage's internal column names into business-friendly names. Point Power BI and Power Automate at the views, never at the raw tables. When Sage upgrades change a column, you fix the view once instead of rebuilding every report.
• Use the Power BI on-premises data gateway to connect the Sage SQL Server to Power BI Service. Refresh on a schedule (hourly is safe; every 15 minutes is fine for most SA workloads).
• For Power Automate, use the SQL Server connector — same gateway, direct query in the flow.

What to avoid: writing to Sage tables directly. Ever. It bypasses Sage's business logic (VAT calculation, GL posting rules, sub-ledger integrity) and voids your Sage support contract. Every write goes through Pattern 2 or Pattern 3.

Pattern 2 — Sage Evolution Web API (or the Sage 200 UK / Intacct REST APIs)

Sage Evolution ships a Web API that exposes the same business logic the desktop client uses — meaning writes go through the correct validation, VAT calculation and GL posting. For any flow that creates or updates data in Sage, this is the right door.

Use it for: posting AP invoices from an invoice automation flow, creating sales orders from a Power Apps mobile form, updating stock adjustments captured on the warehouse floor, creating new customer or supplier records from an onboarding portal.

What to actually do:

• Install and configure the Sage Evolution Web API on your Sage server. Sage's documentation covers the setup; the practical gotcha is that it needs its own service account with rights on the Sage company database.
• In Power Automate, use the HTTP connector (Premium) to call the Web API endpoints. Wrap the calls in a child flow per entity (Post AP Invoice, Create Customer, Post Journal) so business logic is not repeated across ten parent flows.
• Handle Sage-side errors properly. The Web API returns structured errors for VAT mismatches, missing GL accounts, closed periods — surface these into your exceptions queue with the invoice or transaction attached, not as raw JSON.
• Log every posted transaction to a SharePoint list or Dataverse table with the Sage-side reference number. Auditors will ask; developers will thank you.

For Sage 200 UK (not Evolution) the equivalent is the Sage 200 REST API, and for Sage Intacct it is the native REST API. Same pattern, different endpoints.

Pattern 3 — Stage through Dataverse or SharePoint, then batch into Sage

Not everything needs to hit Sage in real time. A mobile stock count captured on the warehouse floor, a delivery confirmation, a job card, an expense claim — these should be captured immediately in a staging layer, validated, approved, and only then posted to Sage as a controlled batch.

Use it for: Power Apps mobile capture, approval workflows, any process where the user needs to work offline or where a supervisor needs to sign off before Sage sees the data.

What to actually do:

• Capture into Dataverse (if you have Power Apps per-app or per-user licences) or SharePoint lists (if you are on the Microsoft 365 base plan). Dataverse gives you relational integrity, role-based security and better performance at scale; SharePoint is free with M365 and fine up to a few hundred thousand rows.
• Run validation in Power Automate — supplier exists in Sage, GL code is valid, amount is within tolerance, period is open. Route failures to an approvals queue, not into Sage.
• Post validated batches to Sage via Pattern 2 (Web API), typically on a schedule (nightly, hourly) or on approval.
• Write the Sage transaction number back to the Dataverse/SharePoint row so users can trace their captured document to the posted transaction.

This is the pattern that protects Sage from bad data. It is also what makes the Power App feel fast — users are writing to a lightweight staging layer, not waiting for Sage to accept a transaction.

Pattern 4 — Replace SSRS reports with Power BI on the same Sage database

Most established Sage 200 sites still run their reporting through SQL Server Reporting Services (SSRS) — invoice-style reports, aged debtors, stock valuation, monthly management packs. SSRS works, but it is a dead-end from an analytics perspective: no drill-through beyond what the report designer built, no self-service, no mobile-friendly rendering, no natural home for combining Sage data with anything else (CRM, POS, e-commerce).

Use it for: replacing SSRS reports with Power BI while keeping Sage 200 as the data source. This is genuinely the fastest way to get analytics value out of a Sage environment.

What to actually do:

• Do not migrate SSRS reports one-for-one. Half your existing SSRS reports are used by two people twice a year. Do an audit first — which reports actually get opened, by whom, how often? Only migrate the ones that earn their keep.
• Build a Power BI semantic model on top of the same SQL views from Pattern 1. Star schema: fact tables for GL transactions, sales, AP, stock movements; dimensions for customer, supplier, stock item, GL account, calendar. This is the layer everyone reports off — not the raw Sage tables.
• Recreate the top 10–15 SSRS reports as Power BI reports on the semantic model. Keep the layout familiar for the first cut — accountants dislike surprises during a migration.
• Then build the self-service dashboards that SSRS could never do — live aged debtors with drill to invoice, live cash position by bank account, live stock cover by warehouse. This is where the Power BI switch actually pays back.
• For pixel-perfect invoice/statement printing that Power BI genuinely doesn't handle well, keep SSRS running for those specific reports. Power BI + residual SSRS for print is a perfectly valid end state.

See our Power BI + Sage 200 integration guide for the semantic model detail, and the Power BI consulting service for how we deliver these migrations.

Which patterns to combine

A healthy Sage-integrated Power Platform environment for a mid-market SA business usually looks like this:

• Pattern 1 for every read: Power BI dashboards, Power Automate lookups, exports to Fabric OneLake for AI/ML work.
• Pattern 2 for every write: AP invoice posting, sales order creation, customer/supplier onboarding.
• Pattern 3 for anything that needs offline capture, approvals, or a controlled batch: mobile stock counts, expense claims, timesheets.
• Pattern 4 as a specific project — replacing legacy SSRS reports with a modern Power BI semantic model on the same Sage database.

Frequently asked questions

Does querying the Sage 200 SQL database directly void my support contract?

Read-only queries against documented tables and views are fine and are the standard integration approach for reporting. Writing directly to Sage tables — bypassing the application's business logic — is what voids support. Sage's own reseller network builds this way.

Can I use the Sage 50cloud connector in Power Automate?

The published Sage connectors in Power Automate target Sage 50cloud (UK/US), Sage Intacct, and Sage Business Cloud Accounting — not Sage 200 Evolution. For Evolution you go via the Web API using the HTTP connector, or via SQL for reads.

What about SSRS reports that use Sage stored procedures?

Some legacy SSRS reports call Sage stored procedures directly. In Power BI you can either replicate the logic in the semantic model (usually preferable — the logic becomes visible and reusable) or call the same stored procedure through DirectQuery for the specific report.

Where does Microsoft Fabric fit in?

Fabric is the right destination for Sage data at scale — OneLake as the single copy, a medallion Lakehouse for cleaned analytics, and Direct Lake semantic models for Power BI. For a single-company Sage 200 site under a few million rows, Pattern 1 (direct SQL to Power BI) is simpler and cheaper. For multi-entity, multi-source (Sage + CRM + POS + e-commerce) analytics, Fabric earns its licence. See our Microsoft Fabric page.

Where to start

Most Sage-integrated Power Platform projects benefit from a short pattern-selection workshop before any code is written — one afternoon to map each use case to the right pattern, agree the SQL view layer, and scope the Web API endpoints you'll need. See our integration consulting service or book a call.