Run SSIS package in Azure ADF with Power query as Source

Run SSIS Project in Azure SSIS-IR with Power query as a Source using Azure File Share and call SSIS Packages in ADF Pipeline – Part 2

In the previous post, we discussed creating an SSIS package and executing it in a local machine, in this post we will discuss how we can shift the SSIS package from a local machine to Azure SSIS – IR.

Deploying SSIS Project in Azure SSIS – IR

After you have finished creating and debugging your SSIS packages in SQL Server Data Tools, you need to deploy your project to the SSIS catalog. Deploying in the Azure cloud is no different than deploying to your on-premises server, but we have different Deployment Models in Azure

Deployment Models

For a bit of history, SSIS on-premises has supported two deployment models for a while:

  1. Package Deployment Model that has been around since the early days of SSIS.  It allows you to deploy packages into file system or SQL Server database (MSDB), and then manage them using the legacy SSIS package store interface on SSMS.
  1. Project Deployment Model that was introduced with SQL Server 2012.  It allows you to deploy projects/packages into SSIS catalog (SSISDB) hosted by SQL Server, and then manage them using the Integration Services Catalogs interface on SSMS.

In the beginning, SSIS in ADF supported only Project Deployment Model where SSISDB is hosted by Azure SQL Database (DB)/Managed Instance (MI).  Over time, we’ve learned that converting SSIS packages from Package Deployment Model into Project Deployment Model can be quite challenging, especially when you have to redesign many of them to replace configuration files with project/package parameters.  On top of that, our on-premises telemetry shows that SSIS instances with Package Deployment Model continue to outnumber those with Project Deployment Model by two to one.

Now, SSIS in ADF also supports Package Deployment Model where packages can be stored in the file system, Azure Files, or MSDB hosted by Azure SQL MI, and managed using the new SSIS IR package store interface on SSMS.  This also turns SSISDB from a potential migration blocker into an optional enhancement and makes the provisioning of SSIR IR much simpler, because bringing your own Azure SQL DB/MI to host SSISDB is not mandatory anymore.

With Project Deployment Model, you can use Integration Services Deployment Wizard on SSDT/SSMS to deploy projects/packages into SSISDB hosted by Azure SQL DB/MI.  With Package Deployment Model, you can use dtutil command prompt utility to deploy packages into Azure Files or MSDB hosted by Azure SQL MI, while switching their protection level at the same time.

In our case, we will use the project deployment model with file share has a location for the project, for further details you can visit Package location: File System (Project)

SSIS Integration Runtime Setup

For detailed instructions visit here

Azure SSIS IR is an Azure Data Factory fully managed cluster of virtual machines that are hosted in Azure and dedicated to run SSIS packages in the Data Factory, with the ability to scale up the SSIS IR nodes by configuring the node size and scale it out by configuring the number of nodes in the VMs cluster.

With Azure-SSIS IR, you can easily run the SSIS packages that are deployed into the SSIS catalog database, hosted in an Azure SQL Database server or an Azure SQL Database Managed Instance using the Project deployment model, or run the packages that are deployed into the file system, Azure Files, or SQL Server MSDB database that is hosted in an Azure SQL Database Managed Instance using the Package Deployment model.

In order to configure the Azure SSIS IR, open the Azure Data Factory using the Azure portal, then from the Overview page select the Author & Monitor option. From the Get Started window, you can configure the Azure SSIS IR from the Manage tab -> Integration Runtimes -> New Azure SSIS IR, or directly from the Configure SSIS Integration option.

For setting up SSIS-IR you can follow the detailed instructions from Microsoft as mentioned above.

The only setting you need to be aware of is that you need to check the following as shown in the figure


All remaining settigs you can do as you required

Once created successfully, ensure that the Azure SSIS IR status is changed to “Running” in order to use it to run the SSIS packages. This can be checked from the automatically opened Manage page, where you can see that the Azure SSIS IR status is Running, with the ability to edit, monitor, start, stop or delete the Azure-SSIS IR from that window, taking into consideration that you need to stop it before performing any change. In addition, you can create a pipeline with Execute SSIS Package activity or view the JSON code of the Azure-SSIS IR from the same page, as shown below

SSIS-IR Running Status

FileShare for SSIS Package(Project) Location

We are going to deploy SSIS Project in filestore since we selected For Package location File System (Project) while setting up SSIS-IR.

Note:- Create two file shares one for logs and another one for package store as shown below

FileShares- Log- Package

Deploying SSIS Packages

Now the Azure-SSIS IR is provisioned and ready for use. The next step is to deploy the SSIS package to the filestore in order to run it under the Azure Data Factory. This can be achieved using the SQL Server Data Tools, SQL Server Management Studio, and the dtutil and AzureDTExec command-line utilities.

Once you build and test the package in visual studio you will get the .ispac file for the project. This file can be obtained from the following location as shown below.

project–> bin–>Development–>.ispac

.ispac file location

Once you have the file location ready, go to Azure Portal upload the .ispac file into the file share location in my case it is ssispackstore.

Once uploaded create a pipeline in ADF with Execute SSIS package as a task, and complete the following settings

Execute SSIS Pkg Settings

After providing all the required information, validate the pipeline settings then click on the Debug button to enable the debug mode of the pipeline execution and monitor the SSIS package execution within the pipeline and ensure that the SSIS package will be executed successfully within the Azure Data Factory without any error, as shown below

Debug Pipeline

Now you can easily publish the pipeline to the production Azure Data Factory environment and create a trigger to schedule the SSIS package execution.


Leave a Comment

Your email address will not be published.