Recently, it happened to me. Again. I delivered another round of SSIS and ADF training, and I forgot to stop an Azure-SSIS integration runtime. I left it running for a few days and it cost me, well… more money than it would have cost me if I had remembered to stop it!

Regarding the “Stop Azure-SSIS IR” Pipeline

I have blogged about stopping an Azure-SSIS IR in the past (Start Azure-SSIS, Execute Package, Stop Azure-SSIS). The pattern described in the Start Azure-SSIS, Execute Package, Stop Azure-SSIS post is sound and awesome. My only issue is the pipeline will report failure if the Azure-SSIS IR is not running when the Web Activity executes the Stop command. Mind you, the operation succeeds. But it reports failure.

Reporting failure when the operation succeeds seriously messes with my CDO (that’s OCD with the letters in the proper order).

Configuring a Web Activity to Stop the IR

Begin by creating a new ADF pipeline as shown here:

You may name your pipeline whatever you wish; I named my pipeline “Stop Azure-SSIS-Files IR” because the name of my Azure-SSIS IR is “Azure-SSIS-Files,” I want to stop it, and I am feeling particularly creative this morning:

In the Activities blade, expand the General category and drag a Web activity onto the pipeline canvas, and then rename the Web activity “Stop Files IR”:

Before we click the Web activity’s Settings tab and configure the URL for the Stop command (as discussed in an upcoming section), let’s configure necessary security.

Configuring Security

You must first configure a Contributor security role. Begin by opening the Azure Portal and clicking the “All resources” link on the left menu. Select the instance of Azure Data factory from the list:

When the ADF page displays, click the “Access control (IAM)” link to navigate to the Access control (IAM) page:

You may search for the Data Factory Managed Identity from the “Check access” tab by setting the Find dropdown to Data Factory, selecting the subscription, and then entering the name of the Azure Data Factory instance in the “Search by name” textbox:

 

Click the “Add” button in the “Add a role assignment” box to begin adding a role to the identity of the ADF instance name:

When the “Add role assignment” blade displays, select Contributor from the Role dropdown and search again for the identity of the ADF instance name:

Click on the aDemo identity to begin adding the identity of the ADF instance name to the Contributor role:

Click the Save button to complete adding the identity of the ADF instance name to the Contributor role. the Role assignments page should now display the identity of the ADF instance name assigned to the Contributor role:

Configuring the Stop Command URL

Return to the Azure Data Factory pipeline.

The Azure Data Factory version 2 REST API provides access to a rich set of operations from which data engineering developers may access ADF metadata and issue commands. The URI for the ADFv2 REST API is management.azure.com. If you click the link you will see a generic response informing you your command is not well-formed:

We want to issue a “stop Azure-SSIS integration runtime” command. To begin, we need the path to the resource. There are many ways to obtain the path to our Azure-SSIS IR. I prefer opening the ADF Monitor page and then clicking “Integration runtimes” to open the Integration runtimes blade:

To view the Integration Runtime details, click on the name of the Azure-SSIS integration runtime you desire to programmatically stop – “Azure-SSIS-Files” in my case:

To obtain the Resource ID of the IR, click the status link (“Stopped” in the image above) to open the Integration Runtime status detail, and then click the “Copy” icon as shown here:

Your Azure-SSIS Integration Runtime resource ID should look something like this:

/subscriptions/<subscription id>/resourcegroups/rgADemo/providers/Microsoft.DataFactory/factories/aDemo/integrationruntimes/Azure-SSIS-Files

To complete the URL for the “Stop Files IR” Web activity, prefix the resource ID with:

https://management.azure.com

Next, supply the suffix:

/stop?api-version=2018-06-01

All together, the URL should appear similar to:

https://management.azure.com/subscriptions/<subscription id>/resourcegroups/rgADemo/providers/Microsoft.DataFactory/factories/aDemo/integrationruntimes/Azure-SSIS-Files/stop?api-version=2018-06-01

Contiune configuring the “Stop Files IR” Web activity by selecting “POST” from the Method dropdown:

Using JSON, add a message the Body property similar to that shown here:

Once the “Stop Files IR” Web activity’s Settings are configured, they should appear similar to this image:

Let’s Test!

The image below shows the Connections > Integration runtimes page. Azure-SSIS-Files is currently running as shown here:

“Running” means at least one of the two configured nodes (virtual machines) are running the background. We can see this by returning to the Monitor > Integration runtimes page and clicking the name of the IR to view the details:

I captured this screenshot after the first node had started. The second node is starting in the screenshot above.

When virtual machines are running, they cost money.

Return to the Author page and click Debug to text-execute the Stop Azure-SSIS-Files IR pipeline:

If all goes as planned, you should be able to observe the Azure-SSIS Integration Runtime “stopping” on the Monitor > Integration runtimes page as seen here:

The “Safely” Part

While the Stop Azure-SSIS-Files IR pipeline will execute and stop the Azure-SSIS-Files IR, what happens if the Azure-SSIS-Files IR is not currently running? The pipeline execution fails as shown here:

As I mentioned earlier in this post, this messes with my CDO.

How may we check to see if an Azure-SSIS Integration Runtime is running? Once again, the Azure REST API to the rescue! In the Azure REST API documentation, there’s a page dedicated to Integration Runtimes:

Documentation is available for the Get Status command here, and the documentation includes a sample request:

https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/
Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}/getStatus?api-version=2018-06-01

The Integration Runtime GetStatus REST API call returns JSON similar to that shown here:

I can hear you thinking, “How do we make the REST API call, and how do we read the JSON response, Andy?” Those are excellent questions. I’m glad you asked!

Making the Azure REST API Call to Obtain Integration Runtime Status

In ADF, how do we make calls to the Azure REST API? We use a Web Activity, just like the web activity we configured to stop the Azure-SSIS IR. Drag a new Web Activity onto the pipeline canvas, and then name it “Get Azure-SSIS-Files Status,” as shown here:

Click the Settings tab and configure the URL from the GetStatus page:

https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/
Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}/getStatus?api-version=2018-06-01

Once the “Get Azure-SSIS-Files Status” web activity is configured, the Settings tab should appear similar to that shown here:

At this time, you may debug the pipeline, knowing the debug execution will fail (if the Azure-SSIS IR is stopped). The goal of a debug execution at this point is to observe the “Get Azure-SSIS-Files Status” web activity output:

When one clicks the Output icon, the Output JSON is displayed. The state property is the property we want, as seen highlighted in the image below:

This is how one makes the Azure REST API call to obtain Integration Runtime Status.

Next, we read the output from the Azure REST API call to obtain Integration Runtime Status.

Reading the JSON Returned From the Azure REST API Call to Obtain Integration Runtime Status

In this case, we desire to execute the “Stop Files IR” web activity if and only if the “Azure-SSIS-Files” integration runtime is in a “Started” state. We can detect the state and execute the “Stop Files IR” web activity when the state is Started using an If Condition activity. Expand the “Iteration & conditionals” category in the Activities blade, and then click a drag an If Condition activity onto the pipeline canvas. Connect a Success output between the “Get Azure-SSIS-Files Status” web activity and the new If Condition activity, and then rename the If Condition activity “If Azure-SSIS-Files is Running” as shown here:

The next step is to check if the returned state value is “Started.” Click the “If Azure-SSIS-Files is Running” if condition activity and click inside the Expression property textbox to surface the “Add dynamic content [Alt + P]” link as shown here:

Click the “Add dynamic content [Alt + P]” link to open the “Add dynamic content” blade. Expand the “Activity outputs” category and click “Get Azure-SSIS-Files Status” to add the output to the dynamic content expression textbox as shown below:

Before I continue configuring this ADF expression, I want to point out that ADF Expression Language is… challenging. I encourage you to learn ADF Expression Language, though. Learning ADF Expression Language is worth the investment in time and frustration.

Update the expression to:

@equals(activity(‘Get Azure-SSIS-Files Status’).output.properties.state,’Started’)

The expression above checks to see if the State property returned from the REST API call configured in the “Get Azure-SSIS-Files Status” web activity equals Started:

 

It’s now time to move the “Stop Files IR” web activity – the web activity we configured way up at the beginning of this post – into the True condition of the “If Azure-SSIS-Files is Running” if condition. I have one neat trick for that…

One Neat Trick

In “If Azure-SSIS-Files is Running” if condition activity, click the Edit icon – the pencil – in the True configuration of the editor as shown below:

When “If Azure-SSIS-Files is Running” if condition activity’s “True activities” editor displays, drag a new Web activity onto the surface:

Return to the pipeline editor by clicking the link to the “”If Azure-SSIS-Files is Running” if condition activity” pipeline link in the breadcrumbs highlighted in the screenshot above.

Click the “Stop Files IR” web activity and then click the “View Source Code” icon to open the JSON code editor for the activity:

Select and copy the JSON for the “Stop Files IR” web activity onto the clipboard, and then close the JSON editor.

Open the code editor for the “Stop Azure-SSIS-Files IR” pipeline by clicking the code icon in the upper right corner of the pipeline editor, as shown here:

Once the JSON editor for the “Stop Azure-SSIS-Files IR” pipeline is open, search the JSON code until you find the label:

“ifTrueActivities”: [

Starting with the opening curly-brace that follows, carefully select the section that defines the ifTrueActivities JSON as shown below:

Paste the clipboard JSON using Ctrl + V to replace the “Web1” web activity with the “Stop Files IR” web activity JSON on the clipboard:

Click the OK button to close the JSON (code) editor and return to the “Stop Azure-SSIS-Files IR” pipeline editor. Click the “trash can” icon on the “Stop Files IR” web activity to delete it from the “Stop Azure-SSIS-Files IR” pipeline:

Note the “If Azure-SSIS-Files is Running” if condition activity’s True activities now contains “1 activities”:

Let’s Test It!

Click Debug to test-execute the pipeline. If the Azure-SSIS-Files IR is running, we expect the pipeline to stop the IR:

Note the highlighted row in the Debug Output informs us the “Stop Files IR” web activity executed and succeed. That means the “Get Azure-SSIS-Files Status” web activity returned a state equal to “Started” and the “If Azure-SSIS-Files is Running” if condition activity’s expression evaluated True.

Once the Azure-SSIS Files IR is stopped, re-execute the “Stop Azure-SSIS-Files IR” pipeline in the debugger and view the output:

The test execution succeeded and the “Stop Files IR” web activity did not execute, because the  “Get Azure-SSIS-Files Status” web activity returned a state not equal to “Started” and the “If Azure-SSIS-Files is Running” if condition activity’s expression evaluated False.

Conclusion

I can now click “Publish all” to deploy this pipeline and configure a trigger to schedule its execution each evening, so if I forget to shutdown the Azure-SSIS-Files Integration Runtime after a demo or online course delivery, it’ll only cost me a fraction of one day.

My CDO is happy.

Need Help?

Enterprise Data & Analytics specializes in helping enterprises modernize their data engineering by lifting and shifting SSIS from on-premises to the cloud. How may we serve you today?