Host SSIS Projects in Azure Data Factory


Azure Data Factory v2 makes it possible to deploy SSIS projects to the Azure cloud. The execution of the packages is done inside a dedicated integration runtime, called the Azure-SSIS IR.

Lift & Shift your SSIS Projects to Azure

If you want to move your SSIS projects to the cloud so you can decommission your on-premises servers, but you don't have the capacity, knowledge, or budget to do a full migration to Azure Data Factory, you can consider a "lift&shift" scenario. Using the Azure-SSIS IR, you can deploy your existing SSIS projects to Azure. The SSIS catalog is hosted in either an Azure SQL DB or in an Azure SQL Server Managed Instance.

add new integration runtime

Development is still done in Visual Studio, you deploy projects with the same tools, SSIS catalog management can still be done with SQL Server Management Studio. The only difference is that orchestration is now done using Azure Data Factory pipelines, which have their own dedicated "Execute SSIS Package" activity.

sample adf pipeline with SSIS activity

What is the Azure-SSIS IR?

The Azure-SSIS integration runtime enables ADF to execute SSIS packages in the Azure cloud. It behaves like an SSIS Scale Out cluster (see the previous section for more info). When you configure a new IR, you can choose how many nodes make up the runtime and what size they have:

azure-ssis ir configuration

You are billed for every second the IR runtime is running. This can be quite costly, but luckily there are APIs provided that can help you to automatically pause and resume the IR when desired.

The SSIS projects and their packages are stored in an SSIS catalog (if you're using the project deployment model) or on Azure Files (if you're using the package deployment model). Developing and managing your projects are almost exactly the same as before, with the added ability to execute your SSIS package in Visual Studio inside the Azure environment instead of on your local machine. When you execute a package through the Azure-SSIS IR in ADF, the runtime will decide on which node of the cluster it will be executed.

The configuration and setup of the Azure-SSIS IR is out of scope for this tutorial, but there are many resources on this site to help you get started:

There's also a free webinar about the lift&shift scenario of SSIS to ADF: Migrating SQL Server Integration Services to the Cloud.

Comments For This Article

get free sql tips
agree to terms