Enterprise-Scale Data Integration with Azure Data Factory, SQL Managed Instance, and SSIS

PeterLo presents a thorough enterprise data integration guide using Azure Data Factory, SQL Managed Instance, and SSIS, guiding engineering teams through secure cloud deployment and end-to-end pipeline setup.

Enterprise-Scale Data Integration with Azure Data Factory, SQL Managed Instance, and SSIS

Author: PeterLo

This step-by-step guide is designed for cloud solution architects and data engineering teams implementing enterprise data solutions on Azure. It covers the integration of Azure Data Factory (ADF) with Azure SQL Managed Instance (SQLMI), the configuration and deployment of SQL Server Integration Services (SSIS) on Azure, and end-to-end data pipeline integration with Microsoft Fabric (Power BI). All instructions reference official Microsoft Learn documentation for further details.

1. Enabling the Azure Data Factory Instance

• Create an Azure Data Factory: Get started with Azure Data Factory
• Permissions: Assign Contributor, Owner, or Administrator roles on your subscription/resource group. Role requirements

2. Granting Initial Access to the Data Engineering Team

• Set ADF Permissions: How to set permissions in ADF?
• Assign Data Factory Contributor Role: This allows your team to author pipelines and manage connections. Data Factory Contributor details

3. Connecting Azure SQL Managed Instance with ADF

• Networking Options
  • Use managed virtual networks with managed private endpoints. More info
  • For hybrid scenarios, use a self-hosted integration runtime.
• Data Movement
  • Copy and transform data in Azure SQL Managed Instance using ADF pipelines.

4. Creating and Configuring SSIS Runtime

• Prerequisites
  • Create an Azure-SSIS integration runtime
• Provisioning
  • Provision the Azure-SSIS integration runtime
  • Choose Enterprise Edition for advanced features
  • Enable Microsoft Entra authentication for secure access
  • Join the runtime to a virtual network; create a subnet in the SQLMI VNET as required

5. Running SSIS Packages in ADF

• Deploy and Run
  • Deploy and run SSIS packages in Azure
  • Migrate on-premises SSIS workloads to Azure Data Factory SSIS

6. Sending Data to Microsoft Fabric (Power BI)

• Ingest Data
  • Use ADF Copy activity to ingest data into Fabric (Power BI)

7. Using ADF in Fabric for Mature, Deployment-Ready Workloads

• ADF Integration in Fabric
  • Mount Azure Data Factory items in Microsoft Fabric for production workloads

References

All steps and best practices are sourced from Microsoft Learn:

• Azure Data Factory documentation: https://learn.microsoft.com/en-us/azure/data-factory/
• SQL Managed Instance: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/
• SSIS on Azure: https://learn.microsoft.com/en-us/azure/data-factory/ssis-overview
• Microsoft Fabric integration: https://learn.microsoft.com/en-us/fabric/data-factory/

---

Published by PeterLo, October 6, 2025

This post appeared first on “Microsoft Tech Community”. Read the entire article here