Azure Data Factory, SQL Managed Instance, and SSIS Implementation

Co-Authored By: Rafia_Aqil​ 

This guide provides step-by-step instructions for enabling Azure Data Factory (ADF), connecting it with Azure SQL Managed Instance (SQLMI), provisioning SSIS runtime, running SSIS packages, and integrating with Microsoft Fabric (Power BI). It is intended for cloud solution architects and data engineering teams deploying enterprise-scale data solutions.

1. Enabling the ADF Instance

• • Create an Azure Data Factory
    Follow the official Microsoft Learn guide to set up your ADF instance.
  • Required Roles
    You must have Contributor, Owner, or Administrator permissions on the subscription or resource group to deploy and configure the ADF resource.

2. Granting Initial Access to the Data Engineering Team

• • Initial Access How to set permissions in ADF ? - Microsoft Q&A
  • Assign Data Factory Contributor Role
    Grant the Data Factory Contributor role to your engineering team on the ADF resource. This enables them to log in, author pipelines, and manage linked services.

3. Connecting Azure SQL Managed Instance with ADF

• • Networking Options
    • Use managed virtual networks and managed private endpoints: Managed virtual network and managed private endpoints - Azure Data Factory | Microsoft Learn.
    • Alternatively, create a self-hosted integration runtime.
  • Data Movement

• • • Copy and transform data in Azure SQL Managed Instance using ADF pipelines: Copy and transform data in Azure SQL Managed Instance - Azure Data Factory & Azure Synapse | Microsoft Learn
  •  
•  
• 4. Creating SSIS Runtime

• • Prerequisites
    • Create an Azure-SSIS integration runtime in Azure Data Factory: Create an Azure-SSIS integration runtime in Azure Data Factory - Azure Data Factory | Microsoft Learn.

• • Provisioning
    • Provision the Azure-SSIS integration runtime: Provision the Azure-SSIS integration runtime - Azure Data Factory | Microsoft Learn

• • • Choose Enterprise Edition for advanced features: Provision Enterprise Edition for the Azure-SSIS Integration Runtime - Azure Data Factory | Microsoft Learn

• • • Enable Microsoft Entra authentication for secure access: Enable Microsoft Entra authentication for Azure SSIS integration runtime - Azure Data Factory | Microsoft Learn

• • • Join the Azure-SSIS integration runtime to a virtual network.

• • • Create a new subnet in the existing SQLMI VNET.

5. Running SSIS Packages in ADF

• • Deploy and Run
    • Deploy and run SSIS packages in Azure using ADF: Deploy and run SSIS packages in Azure - SQL Server Integration Services (SSIS) | Microsoft Learn

• • • Migrate on-premises SSIS workloads to SSIS in Azure Data Factory: Migrate on-premises SQL Server Integration Services (SSIS) workloads to SSIS in Azure Data Factory (ADF) - Azure Data Factory | Microsoft Learn

6. Sending Data to Fabric (Power BI)

• • Ingest Data

• • • Use the Azure Data Factory Copy activity to ingest data into Fabric (Power BI): How to ingest data into Fabric using the Azure Data Factory Copy activity - Azure Data Factory | Microsoft Learn

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

• • ADF Integration in Fabric

• • • Use Azure Data Factory items (Mount) in Microsoft Fabric for robust, production-ready workloads: How to Use Azure Data Factory item (Mount) in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

References: All steps and best practices are sourced from official Microsoft Learn documentations mentioned in this documentation.