Start and Stop SSIS Integration Runtimes with ADF only

Case

I want to stop and start my SSIS Integration Runtime from within my Azure Data Factory pipeline, but I don't want write any code or use other Azure services like Azure Automation or Azure Logic Apps to do this. Is there an Azure Data Factory-only solution where we only use the standard pipeline activities from ADF?

SSIS Integration Runtime

Solution

Yes there is a nocode solution where you use the Web Activity to call the Rest API of Integration Runtimes (as part of ADF), but oddly enough that requires you to give ADF permissions to its own Integration Runtime via its Managed Service Identity (MSI).

1) Give ADF access to ADF via MSI

For this example we will give ADF access to its own resources. Giving access is done via MSI (managed service identity). The minimum role needed is Data Factory Contributor, but you could also use a regular Contributor or Owner (but less is more).

• Go to the Data Factory in the Azure Portal
• In the left menu click on Access control (IAM)
• Click on the +Add button and choose Add role assignment
• Select Data Factory Contributor as Role
• Use Data Factory as Assign access to
• Changing the subscription is probably not necessary
• Optionally enter a (partial) name of your parent ADF (if you have a lot of data factories)
• Select your ADF and click on the Save button

Giving ADF access to its own resources

2) Add Web Activity
In your ADF pipeline you need to add a Web Activity to call the Rest API of the integration runtimes. First step is to determine the Rest API URL. Replace in the string below, the <xxx> values with the subscription id, resource group, data factory name and the name of the integration runtime. The Rest API method we will be using is 'Start' but you can replace that word by 'Stop' to pause the SSIS IR:

https://management.azure.com/subscriptions/<xxx>/resourceGroups/<xxx>/providers/Microsoft.DataFactory/factories/<xxx>/integrationRuntimes/<xxx>/start?api-version=2018-06-01

Example:
https://management.azure.com/subscriptions/a74a173e-4d8a-48d9-9ab7-a0b85abb98fb/resourceGroups/bitools/providers/Microsoft.DataFactory/factories/bitools/integrationRuntimes/bitoolsir/start?api-version=2018-06-01

Second step is to create a JSON message for the Rest API. Well the Rest API doesn't use it, but it is required in the Web activity when you use POST as method. So you just need to create a dummy json message:

{
    "Dummy": "Dummy"
}

• Add the Web activity to your pipeline
• Give it a descriptive name like Start SSIS (or Stop SSIS)
• Go to the Settings tab
• Use the Rest API URL from above in the URL property
• Choose POST as Method
• Add the dummy JSON message from above in the Body property
• Under advanced choose MSI as Authentication method
• Add 'https://management.azure.com/ in the Resource property

Web Activity calling the SSIS IR Rest API

Now run the pipeline by hitting the debug button in the pipeline editor and check the output.

Then Debug the Pipeline to check the stop/start action

3) Retrieve info
By changing the operation in the URL (stop or start) to 'getStatus', you can retrieve the current status of the integration runtime. With this information you could for example first check the status before changing it. The expression in the If condition could be something like:
@equals(activity('Get SSIS IR Status').output.properties.state,'Stopped')

Using 'getstatus' operation to retrieve current status

Status available in output

Conclusion

In this post you learned how easy it is to add a stop and start option in your pipeline to save some money on your Azure bill. Check out my other blog (https://microsoft-bitools.blogspot.com/) for more Rest API solutions in Azure Data Factory.

Fixed IP addresses for ADF Integration Runtimes

Case
I want to give my Integration Runtime access to my sources via a firewall rule and block other machines or services. How do I arrange that?

Firewall exceptions for SSIS IR

Solution
Good news! Microsoft published a list of IP addresses per Azure region for the Integration Runtimes in Azure Data Factory. This means you can narrow down the list of machines accessing your sources. Now only Integration Runtimes from a specific Region (like West Europe) can access it. Perhaps not enough for everybody, but it is better then giving ALL Azure services access to for example your Azure SQL Database.

Note 1: The IP addresses are listed as CIDR. For some firewalls you have to convert those to a IP range. You can use a CIDR to IPv4 calculater to convert them. For example:
40.74.26.0/23 => 40.74.26.0 to 40.74.27.255 (512 hosts in total)

Note 2: Not sure how often this list of IP addresses changes. So you might want to put the URL of the list in your documentation for error handling.

Note 3: Azure Data Factory Data Flows does not use the same IP addresses. A list of all Azure IP addresses can be downloaded as a JSON file. This JSON file gets updated on weekly basis.




Credits Geerten de Kruijf