Skip to content

Power Automate Integration

Jump to bottom
Bert Verbeek edited this page Nov 7, 2025 · 1 revision

Power Automate Integration

This guide explains how to use Power Automate to automatically trigger Azure Synapse pipelines when Business Central exports complete, creating an event-driven data integration solution.

📋 Table of Contents

Overview

Power Automate acts as a mediator between Business Central and Azure Synapse Analytics, enabling:

  • ✅ Automatic pipeline triggering when BC exports complete
  • ✅ Event-driven architecture (no manual intervention)
  • ✅ Per-table consolidation (immediate processing)
  • ✅ Extensible workflow for notifications and custom logic

Note: This integration is designed for Azure Data Lake Storage option only. Microsoft Fabric users should schedule notebooks directly.

Why Use Power Automate?

Without Power Automate

  • Manual pipeline triggering after BC exports
  • Wait for all tables to export before consolidation
  • Batch processing approach
  • Requires user intervention

With Power Automate

  • ✅ Instant triggering: Pipeline starts as soon as a table export completes
  • ✅ No manual steps: Fully automated workflow
  • ✅ Per-table processing: Each table consolidated immediately
  • ✅ Extensible: Easy to add notifications, approvals, error handling
  • ✅ Loosely coupled: BC and Synapse don't directly communicate
  • ✅ Future-proof: Easy to integrate additional services

Use Cases

  • Real-time or near real-time data consolidation
  • Event-driven workflows
  • Multi-step integrations (e.g., export → consolidate → notify)
  • Conditional processing based on export status
  • Integration with Teams, Email, or other services

Inspired by Issue #84 and Issue #87

Architecture

The Power Automate solution consists of multiple Cloud flows:

Power Automate Flow Architecture

Flow Components

  1. BC2ADLS - Trigger from Business Central event (Main flow)

    • Listens for BC business events
    • Validates export status
    • Orchestrates child flows

  2. BC2ADLS - Get Access Token (Child flow)

    • Retrieves Azure AD token
    • Centralized credential management
    • Called by main flow

  3. BC2ADLS - Get Queued/Running Synapse Pipelines (Child flow)

    • Checks for existing pipeline runs
    • Prevents duplicate executions
    • Returns active pipeline count

  4. BC2ADLS - Enqueue Azure Synapse Pipeline (Child flow)

    • Triggers Synapse pipeline
    • Passes parameters (container, entity, etc.)
    • Returns execution ID

Data Flow

Business Central Export Completes
          ↓
Business Event Triggered
          ↓
Power Automate Receives Event
          ↓
Validate Export Status = Success
          ↓
Get Azure AD Access Token
          ↓
Check for Running Pipelines
          ↓
If No Conflicts → Trigger Synapse Pipeline
          ↓
Pipeline Consolidates Table Data

Prerequisites

Before setting up Power Automate integration:

  • ✅ Azure Data Lake Storage configured (setup guide)
  • ✅ Azure Synapse workspace with pipelines deployed
  • ✅ Business Central with bc2adls extension installed
  • ✅ Power Automate license (included with Dynamics 365/Microsoft 365)
  • ✅ Service Principal with Synapse permissions
  • ✅ Power Platform environment connected to BC tenant

Setup Steps

Step 1: Import Power Automate Solution

  1. Download the solution file: powerAutomate/bc2adls_1_0_0_0.zip
  2. Navigate to Power Automate portal
  3. Select your environment
  4. Go to Solutions in the left navigation
  5. Click Import solution
  6. Click Browse and select bc2adls_1_0_0_0.zip
  7. Click Next
  8. Review the solution details
  9. Click Import

Wait for the import to complete (may take a few minutes).

📖 Learn more about importing solutions

Step 2: Create Service Principal for Synapse

You need a Service Principal with permissions to execute Synapse pipelines.

Option A: Use Existing Service Principal

If you already created one for bc2adls setup:

  1. Use the same Application ID and Secret
  2. Skip to granting Synapse permissions below

Option B: Create New Service Principal

  1. Navigate to Azure Portal > Azure Active Directory > App registrations
  2. Click New registration
  3. Provide a name (e.g., "BC2ADLS-PowerAutomate")
  4. Select Accounts in this organizational directory only
  5. No redirect URI needed for this one
  6. Click Register
  7. Note the Application (client) ID and Directory (tenant) ID
  8. Go to Certificates & secrets > New client secret
  9. Add description and expiration
  10. Click Add and copy the secret value

Grant Synapse Permissions

  1. Open Azure Portal > Your Synapse workspace
  2. Navigate to Access Control (IAM)
  3. Click Add > Add role assignment
  4. Select role: Synapse Contributor or Synapse Administrator
  5. Click Next
  6. Click Select members
  7. Search for your service principal name
  8. Select it and click Select
  9. Click Review + assign

Synapse Access Control

Step 3: Configure BC2ADLS - Get Access Token Flow

This flow manages authentication tokens for Synapse API calls.

  1. In Power Automate, go to Solutions > BC2ADLS
  2. Open the BC2ADLS - Get Access Token flow
  3. Click Edit
  4. Find the Initialize variable actions at the top
  5. Update the following variables:
Variable Value Description
varTenantId Your tenant ID From Step 2
varClientId Your application ID From Step 2
varClientSecret Your client secret From Step 2
varResource https://dev.azuresynapse.net Synapse API endpoint

Configure Get Access Token

  1. Click Save

⚠️ Security Note:

  • The client secret is stored as plain text in the flow
  • Access tokens are visible in run history
  • For better security, use Azure Key Vault connector to store secrets
  • Or configure Managed Identity authentication on HTTP actions

Step 4: Configure BC2ADLS - Trigger Flow

This is the main flow that orchestrates the entire process.

  1. Open the BC2ADLS - Trigger from Business Central event flow
  2. Click Edit
  3. Find the Initialize variable actions
  4. Update the following variables:
Variable Value Description
varAzureSubscriptionId Your Azure subscription ID Found in Azure Portal
varAzureResourceGroupName Your resource group name Where Synapse workspace is located
varAzureSynapseWorkspaceName Your Synapse workspace name From Synapse setup
varPipelineName Consolidation_OneEntity Synapse pipeline to trigger

Configure Trigger Flow

Optional variables:

  • varDeleteDeltas: true or false - Delete deltas after successful consolidation
  • varSparkPoolName: Spark pool name or leave blank
  1. Click Save

Step 5: Enable the Flow

  1. Close the flow editor
  2. In the solution, find BC2ADLS - Trigger from Business Central event
  3. Click the ... menu > Turn on
  4. Verify status changes to On

Step 6: Configure Business Central Business Events

Enable the business event that triggers the flow:

  1. Open Business Central
  2. Search for Business Event Catalog
  3. Find event: ADLSE Execution - On Table Export Completed
  4. Set Enabled to Yes
  5. Configure endpoint:
    • Endpoint URL: Your Power Automate trigger URL (auto-configured if using connector)
    • Authentication: OAuth 2.0

Alternative: Use BC Connector in Power Automate

  • The flow already includes the BC connector
  • When you enable the flow, it automatically subscribes to the event
  • No manual Business Event configuration needed

How It Works

Workflow Execution

Step 1: Business Central Export

  1. BC exports a table to Azure Data Lake
  2. Export completes successfully
  3. BC fires business event: On Table Export Completed

Step 2: Power Automate Trigger

  1. Flow receives business event with payload:

    {
  "Company": "CRONUS International Ltd.",
  "TableID": 18,
  "TableName": "Customer",
  "ContainerName": "businesscentral",
  "Status": "Success",
  "Timestamp": "2025-01-15T10:30:00Z"
}

  2. Flow checks Status field

    • If not "Success": Flow stops (no pipeline trigger)
    • If "Success": Continue to next step

Step 3: Get Access Token

  1. Flow calls BC2ADLS - Get Access Token child flow
  2. Child flow requests OAuth token from Azure AD: 
    POST https://login.microsoftonline.com/{tenant}/oauth2/token
{
  "grant_type": "client_credentials",
  "client_id": "...",
  "client_secret": "...",
  "resource": "https://dev.azuresynapse.net"
}
  3. Returns access token to main flow

Step 4: Check for Running Pipelines

  1. Flow calls BC2ADLS - Get Queued/Running Synapse Pipelines

  2. Queries Synapse API for active runs:

    GET https://management.azure.com/.../pipelineruns?api-version=2020-12-01
Filter: status=InProgress OR status=Queued
Filter: parameters.entityName = current table

  3. Returns count of matching pipelines

  4. If count > 0: Flow stops (prevents duplicate runs)

  5. If count = 0: Continue to trigger

Step 5: Trigger Synapse Pipeline

  1. Flow calls BC2ADLS - Enqueue Azure Synapse Pipeline
  2. Child flow triggers pipeline: 
    POST https://{workspace}.dev.azuresynapse.net/pipelines/Consolidation_OneEntity/createRun?api-version=2020-12-01
{
  "containerName": "businesscentral",
  "entityName": "Customer",
  "deleteDeltas": true,
  "sparkpoolName": ""
}
  3. Synapse returns run ID
  4. Pipeline starts executing

Step 6: Pipeline Execution

  1. Consolidation_OneEntity pipeline runs
  2. Consolidates deltas for the specific table
  3. Updates CDM manifest
  4. Creates shared metadata table (if Spark pool specified)
  5. Deletes deltas (if deleteDeltas = true)

Comparison: Manual vs. Power Automate

Aspect Manual Trigger Power Automate
Trigger User clicks in Synapse Studio Automatic on BC export complete
Pipeline Consolidation_AllEntities Consolidation_OneEntity
Timing After all exports complete Immediately per table
Tables All tables at once One table at a time
Intervention Manual Automatic
Delay Batch (e.g., hourly/daily) Real-time
Flexibility Simple Extensible workflow

Monitoring and Troubleshooting

Monitoring Flow Execution

View Run History

  1. Open Power Automate portal
  2. Go to My flows
  3. Find BC2ADLS - Trigger from Business Central event
  4. Click on the flow name
  5. View 28-day run history

Each run shows:

  • Start time and duration
  • Status (Succeeded, Failed, Cancelled)
  • Inputs and outputs for each action
  • Error messages if failed

Check Pipeline Execution

  1. Open Synapse Studio
  2. Go to Monitor > Pipeline runs
  3. Filter by pipeline name: Consolidation_OneEntity
  4. Look for runs with your table name in parameters

Pipeline Runs

Common Issues

Flow Not Triggering

Problem: BC export completes but flow doesn't run

Solutions:

  • ✅ Verify flow is turned on
  • ✅ Check Business Central business event is enabled
  • ✅ Ensure Power Automate environment is correct
  • ✅ Verify BC and Power Platform are in same tenant
  • ✅ Check flow Connection References are valid

Authentication Failures

Problem: "401 Unauthorized" or "403 Forbidden" errors

Solutions:

  • ✅ Verify service principal client ID and secret are correct
  • ✅ Check service principal has Synapse Contributor role
  • ✅ Ensure token resource is https://dev.azuresynapse.net
  • ✅ Verify tenant ID is correct
  • ✅ Check client secret hasn't expired

Pipeline Not Starting

Problem: Flow succeeds but pipeline doesn't start

Solutions:

  • ✅ Check Synapse workspace name is correct
  • ✅ Verify pipeline name: Consolidation_OneEntity
  • ✅ Ensure pipeline exists in Synapse workspace
  • ✅ Check API version in HTTP request
  • ✅ Review Synapse workspace firewall rules

Duplicate Pipeline Runs

Problem: Multiple pipelines running for same table

Solutions:

  • ✅ Verify Get Queued/Running Pipelines flow is working
  • ✅ Check filter logic in that flow
  • ✅ Ensure proper delay between checks
  • ✅ Review BC export isn't being triggered multiple times

Debugging Tips

Enable Detailed Logging

  1. Edit the flow
  2. For each HTTP action, add Headers: 
    Content-Type: application/json
  3. Check Inputs and Outputs in run history

Test Individual Flows

  1. Test Get Access Token flow first
  2. Verify it returns a valid token
  3. Test Get Queued/Running Pipelines with manual inputs
  4. Test Enqueue Pipeline independently
  5. Finally test the main trigger flow

Use Flow Checker

  1. Edit the flow
  2. Click Flow checker (top right)
  3. Review warnings and errors
  4. Fix issues before saving

Extending the Solution

Add Email Notifications

Add a Send an email action after pipeline trigger:

  1. Edit the main flow
  2. Add action after Enqueue Pipeline
  3. Select Office 365 Outlook > Send an email (V2)
  4. Configure:
    • To: Your email
    • Subject: BC2ADLS: Table @{triggerBody()?['TableName']} exported
    • Body: Include table details and pipeline run ID

Add Teams Notifications

  1. Add Microsoft Teams connector
  2. Add action: Post message in a chat or channel
  3. Configure channel and message
  4. Include export status and links

Add Conditional Logic

Add conditions based on table name or company:

Condition: 
  triggerBody()?['TableName'] equals 'Customer'
  
If yes:
  Trigger pipeline with special parameters
  
If no:
  Trigger pipeline with standard parameters

Add Error Handling

  1. Add Scope action around pipeline trigger
  2. Add Configure run after to handle errors
  3. On failure:
    • Send notification
    • Log to SharePoint/SQL
    • Retry with exponential backoff

Integrate with Other Services

Easily add integrations:

  • ✅ Azure Logic Apps for complex workflows
  • ✅ SharePoint for audit logging
  • ✅ SQL Database for run tracking
  • ✅ Azure Monitor for centralized logging
  • ✅ Azure Functions for custom processing

Best Practices

Security

✅ Use Azure Key Vault for secrets

  • Store client secret in Key Vault
  • Use Azure Key Vault connector in flow
  • Retrieve secret dynamically

✅ Use Managed Identity

  • Configure flow with System-Assigned Managed Identity
  • Grant identity access to Synapse
  • Remove client secret from flow

✅ Limit permissions

  • Grant minimum required roles
  • Use separate service principals for different environments
  • Regularly rotate secrets

Performance

✅ Parallel processing

  • Multiple tables can export and consolidate simultaneously
  • Power Automate handles concurrency

✅ Optimize checks

  • Keep Get Running Pipelines logic efficient
  • Filter by parameters to reduce results

✅ Monitor limits

  • Power Automate has execution limits
  • Monitor flow runs per day
  • Consider premium connectors if needed

Maintenance

✅ Document customizations

  • Add comments in flow actions
  • Maintain separate documentation

✅ Version control

  • Export solution regularly
  • Store in source control (Git)
  • Track changes

✅ Test in non-production

  • Test flow changes in sandbox environment
  • Verify with test data
  • Deploy to production after validation

Additional Resources

Documentation

Related Guides

Community

Next: Execution Guide | Azure Data Lake Setup | Back to Home

Clone this wiki locally