Azure-SSIS Integration Runtime Start-up and Shutdown with Webhooks - Part 2

By:   |   Comments (2)   |   Related: > Azure Data Factory


In part 1 of this tip, we demonstrated how to setup an Azure Automation account and how to create a PowerShell runbook to automatically start or stop an Azure-SSIS Integration Runtime. In this second part, we'll look into further automation of this solution by calling the runbook from an Azure Data Factory pipeline through webhooks.


If you haven't already, please refer to part 1 of this tip, where we configure the Azure Automation account and the PowerShell runbook, since we build further upon those artifacts in this tip. We assume though an Azure Data Factory (ADF) service has already been created, as well as an Azure-SSIS IR. The tip Configure an Azure SQL Server Integration Services Integration Runtime provides more information on how to set those up.

Starting and Stopping the Integration Runtime in an ADF Pipeline.

Creating the Webhooks

Runbooks can be started through a web interface using an HTTP request, the so-called webhook. By creating a webhook, we can start our runbook by using doing such an HTTP request from an ADF pipeline. We're going to create two webhooks: one to start the IR, and one to shut it down. Go to the runbook we created in part 1, and click on Webhook.

create webhook

In the newly opened blade, choose to create a new webhook.

create new webhook

Specify a name and an expiration date. If this webhook is for production use, you might want to choose a date far into the future. Copy and paste the URL and save it somewhere, because once the webhook is created you cannot retrieve it anymore!

specify webhook properties

Once it is created, go to the parameters and run settings menu option:

go configure parameters

In the parameter pane, fill in all the required parameters of the runbook:

specify parameters

When everything is specified, go back to the webhook blade and click Create to finish.

finish creating webhook

You can repeat the same process to create a webhook to stop the Azure-SSIS IR. Specify STOP for the command parameter and don't forget to save the URL! You can find an overview of all available webhooks for a runbook when you go to Webhooks (under Resources) in the left menu.

Specify STOP for the command parameter and donít forget to save the URL

Creating the Azure Data Factory Pipeline

We're going to create an ADF pipeline in which we're going to call the webhooks we just created. In ADF, click on the ellipsis next to Pipelines and choose Add Pipeline.

add new pipeline

In the newly created pipeline, drag a Web activity onto the canvas.

add web activity

In the General tab, give the activity an appropriate name and leave the default settings.

configure web activity - general

In the Settings tab, copy paste the URL from the webhook configured to start the Azure-SSIS IR.

configure web settings

Choose POST as http method. We don't use the body to pass anything into the webhook, so you're free to specify what you want. Drag another web activity onto the canvas. This one will be used to stop the IR once all tasks have been performed in the pipeline. Copy paste the URL from the other webhook:

web activity to stop the IR

Of course, you're not creating an ADF pipeline to start the IR runtime just to shut it immediately down again. Most likely you'll want to run some SSIS packages in between. The problem with the Web activity is that it is asynchronous: it doesn't wait until the IR is actually started. Rather, the next activity is immediately started right after the HTTP POST message is sent to the webhook. This means we need to implement a mechanism that checks if the IR has actually been started or not (which might take up to 20-30 minutes). Add a Stored Procedure activity to the canvas and connect it to the Web activity starting the IR:

stored procedure activity

This Stored Procedure activity needs to connect to the SQL Server instance hosting the SSISDB database, so you need to create a linked service in the connections tab:

create new linked service

You can find an example of creating a linked service in the tip Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1.

In the SQL Account tab, choose the linked service you just created:

choose linked service

In the Stored Procedure tab, we're going to execute a custom SQL script with the sp_executesql stored procedure. Check the Edit checkbox, so you can enter the stored procedure name manually, instead of searching for it in the database.

specify sp name

Click on New to add a parameter. Specify stmt as name, string as type and the following script as value:

-- Wait until Azure-SSIS IR is started
    SELECT 1 FROM [SSISDB].[catalog].[worker_agents]
    WHERE   IsEnabled       = 1
    WAITFOR DELAY '00:00:30';
WAITFOR DELAY '00:05:00'; -- wait an extra 5 minutes because the Azure-SSIS isn't somehow always ready

The final configuration looks like this:

finish sp configuration

The script checks an SSIS catalog view for the number of working agents (these are the nodes of the Azure-SSIS IR) are started in the last minute. The script will be looping for quite some time, until the IR is started. I noticed however that even though the worker agents were found, the IR itself wasn't somehow quite ready yet. Starting an SSIS package still resulted in an error. That's why I added an extra wait time of 5 minutes at the end, just to make sure the IR is up and ready.

After this activity, you can add other activities that will run your SSIS packages. These can either be other Stored Procedures activities or Execute SSIS Packages activities. The entire flow then looks something like this pipeline:

final adf pipeline

You can find more information about running SSIS packages in the Azure-SSIS IR in these tips:

All that's left is to configure a trigger on the pipeline to schedule the execution of the SSIS packages.


In part 1 and part 2 of this tip we showed how you automate the startup and shutdown of an Azure-SSIS Integration Runtime cluster. The solution uses a combination of Azure Runbooks, webhooks and Azure Data Factory pipelines. Keep in mind that starting the Azure-SSIS IR on demand adds a wait time of up to 30 minutes to your ETL window.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, August 12, 2020 - 1:53:00 AM - Koen Verbeeck Back To Top (86285)
Hi Higor,

that's not normal. In a typical execution, the Web Activity should stop once the webhook has done. Anyway, ADF now has an API which you can use to start and stop the IR. You can find more info in the docs:

You can check the status of the API, check out this blog post:

Friday, August 7, 2020 - 4:37:48 PM - Higor Grilo Back To Top (86254)
Hi Koen,
I've made something similar on my ADF, but when i trigger the pipeline, the activity that starts the IR never ends, how can i get the status of the webhook call and go to the next activity?

get free sql tips
agree to terms