Azure Synapse Pipeline Trigger from Excel

Trigger Azure Synapse Analytics pipelines directly from Microsoft Excel using VBA and Azure Functions.

Overview

This solution allows users to trigger Azure Synapse pipelines with a simple button click in Excel. It consists of:

• Azure Function (Python) - Serverless API that communicates with Azure Synapse
• Excel VBA Module - User-friendly interface for triggering pipelines
• Comprehensive Documentation - Setup guides for administrators and end users

Features

✅ Simple Excel Interface - One-click pipeline triggering from Excel ✅ Secure Authentication - Uses Azure Managed Identity for secure access ✅ Error Handling - Clear error messages and success notifications ✅ Flexible - Easy to configure for different pipelines ✅ Scalable - Serverless architecture, no infrastructure to manage ✅ Optional Parameters - Support for pipeline parameters

Architecture

Excel (VBA) → Azure Function (Python) → Azure Synapse Analytics

1. User clicks button in Excel
2. VBA code sends HTTP POST request to Azure Function
3. Azure Function authenticates using Managed Identity
4. Function triggers the specified Synapse pipeline
5. Response is displayed to user in Excel

Quick Start

For End Users

If you just want to use the Excel trigger (not deploy the Azure Function):

1. Download TriggerPipeline.bas from the excel/ folder
2. Follow the User Setup Guide
3. Import the VBA code into Excel
4. Create a button and start triggering pipelines!

For Administrators

If you need to deploy the Azure Function:

1. Follow the Deployment Guide
2. Configure environment variables
3. Enable Managed Identity
4. Grant Synapse permissions
5. Deploy the function code

Repository Structure

.
├── TriggerPipelineFunction/     # Azure Function code
│   ├── __init__.py              # Main function handler
│   ├── config.py                # Configuration management
│   ├── synapse_client.py        # Synapse API client
│   ├── validator.py             # Request validation
│   ├── response.py              # Response formatting
│   └── exceptions.py            # Custom exceptions
├── excel/                       # Excel VBA code
│   ├── TriggerPipeline.bas      # VBA module
│   └── EXCEL_SETUP.md           # Excel setup instructions
├── tests/                       # Unit and integration tests
│   ├── test_integration.py      # End-to-end tests
│   ├── manual_test.py           # Manual testing script
│   └── ...
├── .kiro/specs/                 # Feature specifications
├── DEPLOYMENT.md                # Azure deployment guide
├── USER_SETUP_GUIDE.md          # End user setup guide
├── TESTING.md                   # Testing documentation
├── requirements.txt             # Python dependencies
└── README.md                    # This file

Documentation

• Deployment Guide - Deploy the Azure Function
• User Setup Guide - Set up Excel for end users
• Testing Guide - Test the function locally and in Azure
• Excel Setup - Detailed Excel configuration

Current Configuration

• Azure Function: pipelinetrigger-bpbzera6dufpahfq.southafricanorth-01.azurewebsites.net
• Synapse Workspace: autolineanalytics
• Resource Group: GoscorIT
• Subscription: Azure subscription 1
• Default Pipeline: A_Lai_Accounts
• Region: South Africa North

Requirements

Azure Function

• Python 3.9+
• Azure Functions Core Tools 4.x
• Azure CLI
• Azure subscription with appropriate permissions

Excel

• Microsoft Excel 2016 or later
• Windows PC
• Macros enabled
• Network access to Azure

Installation

1. Clone the Repository

git clone https://github.com/KFLATELA/Synapse-data.git
cd Synapse-data

2. Deploy Azure Function

Follow the Deployment Guide for detailed instructions.

Quick deploy:

# Set subscription
az account set --subscription "Azure subscription 1"

# Deploy function
func azure functionapp publish PipelineTrigger

3. Set Up Excel

Follow the User Setup Guide for detailed instructions.

Quick setup:

1. Open Excel
2. Press Alt + F11
3. Import excel/TriggerPipeline.bas
4. Create a button and assign the macro

Usage

Trigger a Pipeline from Excel

1. Open your Excel workbook with the VBA code
2. Click the "Trigger Pipeline" button
3. Wait for confirmation message
4. Check Synapse Studio for pipeline run status

Trigger with Parameters

Modify the GetPipelineParameters() function in the VBA code to read parameters from Excel cells:

Private Function GetPipelineParameters() As String
    Dim inputPath As String
    inputPath = Range("InputPath").Value
    
    GetPipelineParameters = "{"
    GetPipelineParameters = GetPipelineParameters & """inputPath"": """ & inputPath & """"
    GetPipelineParameters = GetPipelineParameters & "}"
End Function

Change Pipeline Name

Edit the VBA code constant:

Const PIPELINE_NAME As String = "Your_Pipeline_Name"

Testing

Local Testing

# Set environment variables
$env:SYNAPSE_WORKSPACE_NAME="autolineanalytics"
$env:AZURE_SUBSCRIPTION_ID="2d5273ef-b491-446f-8879-4edc2c4a5cc1"
$env:SYNAPSE_RESOURCE_GROUP="GoscorIT"

# Run manual test
python tests/manual_test.py --pipeline A_Lai_Accounts

Integration Tests

# Run all tests
python -m pytest tests/

# Run specific test
python -m pytest tests/test_integration.py -v

See TESTING.md for more details.

Security

Authentication

• Azure Function uses Managed Identity for authentication
• No credentials stored in code
• Function key required for API access

Best Practices

• Rotate function keys every 90 days
• Use IP restrictions if possible
• Monitor pipeline runs in Synapse Studio
• Don't share VBA code outside your organization

Function Key Management

If you need to rotate the function key:

1. Generate new key in Azure Portal
2. Update the VBA code with new URL
3. Redistribute to all users

Troubleshooting

Common Issues

Excel: "Macros have been disabled"

• Enable macros in Trust Center settings

Excel: "Error triggering pipeline"

• Check internet connection
• Verify function URL is correct
• Check if corporate firewall blocks Azure

Azure: "401 Unauthorized"

• Verify Managed Identity is enabled
• Check Synapse RBAC permissions

Azure: "500 Configuration Error"

• Verify environment variables are set
• Check all three variables are present

See USER_SETUP_GUIDE.md for more troubleshooting tips.

Contributing

This is an internal project for Goscor IT. For changes or improvements:

1. Create a feature branch
2. Make your changes
3. Test thoroughly
4. Create a pull request

Monitoring

View Function Logs

az webapp log tail --name PipelineTrigger --resource-group GoscorIT

View Pipeline Runs

1. Open Synapse Studio
2. Go to Monitor → Pipeline runs
3. Filter by pipeline name or run ID

Maintenance

Update Function Code

func azure functionapp publish PipelineTrigger

Update Environment Variables

az functionapp config appsettings set --name PipelineTrigger --resource-group GoscorIT --settings KEY=VALUE

Restart Function App

az functionapp restart --name PipelineTrigger --resource-group GoscorIT

Version History

v1.0.0-working (Current)

• Initial working version
• Azure Function deployed to production
• Tested with A_Lai_Accounts pipeline
• Excel VBA module complete
• Full documentation

Support

For issues or questions:

• Check the User Setup Guide
• Review Troubleshooting section
• Contact Goscor IT Azure administrator
• Check Synapse Studio for pipeline run details

License

Internal use only - Goscor IT

Authors

• Kflatela - Initial development and deployment

Acknowledgments

• Azure Functions documentation
• Azure Synapse Analytics documentation
• Python azure-identity and azure-synapse-artifacts libraries

---

Status: ✅ Production Ready

Last Updated: November 2025

Maintained By: Goscor IT