Saturday, 13 February 2021

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.

No comments:

Post a comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...