Lift and Shift On-Premise SSIS Packages in ADF V2

These days databases are being migrated from an On-Premise SQL Server to Azure database services like Azure SQL Database, Azure SQL Data warehouse, etc. So, SSIS will also need to be migrated to Azure cloud service. For this Microsoft provides a cloud version of integration service known as Azure Data Factory (ADF).

ADF is benefitted in below scenarios:

• If any one of the data source or destination is in Azure.
• If you want to work on a data integration workflow without investing time/money on setting up environments.
• ADF provides more flexibility to process and transform data according to the new approach of data warehouse (ELT).
• It provides computation services to process data in HDInsight (Hive, Pig) and USQL (Azure data lake analytics).
• If you want to implement Azure Analytics Integration i.e feed data to Azure Machine Learning model and processing data with USQL.

Business Challenge

If an organization has decided to migrate its database from On-premise to Azure database services and wants to use existing ETL and data integration workflow then SSIS solution can also be migrated to Azure in its cloud-based integration services ADF V2.

Migrating SSIS Solution to Azure

Microsoft's recent release of ADF V2 provides Azure-SSIS Integration Runtime to allow the execution of on-premise SSIS projects on Azure’s Cloud resources. While configuring this integration runtime SSIS catalog can be set up with any one of these database flavors.

• Azure SQL database.
• Azure SQL Database managed instance.

After Setting up SSIS Integration Runtime, SSIS solution can be deployed in integration services catalog in the following ways:

• Using Project Deployment Wizard
• Using Powershell
• Using Visual Studio

ADF provides different activities to run the various computation services. “Execute SSIS package” activity is used to run the SSIS packages in the ADF pipeline. In this Activity, two settings are required:

• Name of Azure SSIS Integration runtime.
• Location of the package.

Migrated SSIS packages in Azure can be scheduled using Trigger and monitored with the below tools:

• Azure Data Factory Monitor: It provides the detail of ADF execution and status of the pipeline if it is executed or not.
• Integration Services Catalogue: After connecting to SSISDB in SSMS execution reports can be accessed for the SSIS project. It loads a page with information of all the executions of SSIS Packages.