TL;DR:

To read the value portion of a key-value pair stored in JSON in an ADF pipeline, interrogate properties.<key>, where key is the key-part of the key-value JSON pair.

Introduction

Last month I wrote a post titled Stop an Azure-SSIS Files Integration Runtime (Safely). The post generated a bunch of hits, which is awesome. The post is also a bajillion pages long, which is… less-awesome. In all honesty, that post is more like a chapter in a book. One of the reasons for that is I am working on a couple book projects right now, so I’ve been mostly writing – wait for it – book chapters.

One of the topics in Stop an Azure-SSIS Files Integration Runtime (Safely) is the Azure Data Factory version 2 (V2) REST API. If you want to learn more about the ADF REAST API, there is simply no better place to start than that link.

A Funny Thing Happened…

First, I need to share with you that I am not self-taught. I am community-taught. I’ve learned everything I’ve learned by reading blog posts and books and attending presentations and webinars. I try to remember where I learned stuff to give the person who taught me credit. I fail to do this more often than I succeed, durnit.

The book chapter upon which I am currently focused deals with an SSIS files-based framework written in Azure Data Factory. I can hear some of you thinking, “What? Why, Andy?” I’m glad you asked! A lot of legacy SSIS exists in on-premises enterprise data engineering / integration stores. A lot of those SSIS packages are executed from on-premises file systems. The Microsoft Azure-SSIS team realizes this and, about a year ago, released new Azure-SSIS Integration Runtime functionality that supports executing SSIS packages stored in Azure File Shares (for more information, see How To: Execute Azure-SSIS Packages From Azure Files). The Azure-SSIS team did this to reduce friction for enterprises moving from on-premises data integration / engineering to cloud-based data integration / engineering. For some enterprises, the transition is easy. For other enterprises – especially those with 15 years’ worth of legacy SSIS running on-premises? The transition is no easy task.

Pop Quiz: What’s another name for “legacy code?”
Answer: “Code that Just Works.”

Back to the funny thing…

As I was describing some challenges of building the Azure Data Factory version of a SSIS files-based framework to my brothers and friends, Joel Cochran (@joelcochran) and Shannon Lowder (@shannonlowder), Joel suggested I try calling a child pipeline to solve a particularly tough issue with which I grappled. Shannon shared a different piece of the puzzle – using an ADF REST API function I had not considered. The answer? I did both.

So I thought I’d blog about it. And here we are.

Calling the ADF REST API

About halfway through Stop an Azure-SSIS Files Integration Runtime (Safely) I use a Web activity to obtain the status of an Azure-SSIS Integration Runtime. Why do I do this? Because if your Azure-SSIS IR is left running, it may cost you more money than you desire. It’s especially troubling when your Azure-SSIS IR is left running and you (or your enterprise) are not using it. Then you are spending money for nothing.

In my opinion, there are two keys to becoming comfortable with the ADF REST API:

1. Learning how to call the API
2. Learning how to read the API’s response

The first key?

Learning to Call the REST API by Configuring the URL setting in the Web activity

On the Settings tab, 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

The second key?

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 can be… challenging. I encourage you to learn ADF Expression Language, though. Learning ADF Expression Language is worth the investment.

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:

That’s It

To read the value portion of a key-value pair stored in JSON in an ADF pipeline, interrogate properties.<key>, where key is the key-part of the key-value JSON pair.

Conclusion

I think this nugget – kind of buried in a long blog post – deserves a post of its own. Once you get the hang of it, interrogating JSON responses in Azure Data Factory pipelines is cool.

Learn ADF and SSIS from Me!

Check out training at Enterprise Data & Analytics!

Need Help?

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