Summarise An Azure Data Factory ARM Template Using T-SQL

While documenting a customers data platform solution I decided it would be far easier if we could summarise the contents of a fairly complex Data Factory using its ARM Template. So, this is what I’ve done using T-SQL to parse the ARM Template JSON and output of series of tables containing details about the factory components. Process overview:

This technique could of course be applied to any ARM template. However, the below script targets the Data Factory resource types and expected template structure.

Getting the ARM Template

There are plenty of ways to get your Data Factory ARM Template. Via PowerShell etc. To keep things simply I just downloaded it from the developer portal UI.

Once you’ve downloaded the ZIP file, grab the arm_template.json file and copy its contents into a suitable database table. It really doesn’t need to be complicated, unless you want it to be.

I created a single field table and pasted the JSON via the SSMS table editor! I know, I know, don’t judge me 🙂 It was actually the easiest way to avoid escaping characters and URL’s within the JSON. I was tempted to write a BULK INSERT statement, but went for the manual option in the end.

JSON Parsing with T-SQL

To parse the content of the ARM template I used the T-SQL OpenJSON table-valued function, both directly, then cross applied it to access the nested levels of the JSON. Snippet below. Make sure your running compatibility level 130 or higher for this feature.

The complete script is available here:

---

Code in Blog Support GitHub repository.
https://github.com/mrpaulandrew/BlobSupportingContent

---

As you’ll each statement within the script is decoupled from the rest if you want to chop bits out and make it more tailored to the details you want from your Data Factory.

Output Tables

Once you’ve executed the T-SQL you’ll get the following result set. An example of the data can be seen from my fun factory on the right.

---

• Data Factory Name
• Summary
  • Component Type
  • Count
• Pipeline Details
  • Name
  • Description
  • Folder Name
  • Activity Count
• Activity Details
  • Name
  • Type
  • Description
  • Belongs To Pipeline
• Linked Service Details
  • Name
  • Type
  • Using Key Vault
• Dataset Details
  • Name
  • Type
  • Folder Name
  • Connected Linked Service
• Integration Runtime Details
  • Name
  • Type
• Data Flow Details
  • Name
  • Type
• Trigger Details
  • Name
  • Type
  • Status

---

I hope you found this useful and can also apply this when documenting your production Data Factories.

Many thanks for reading.