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.

5 thoughts on “Summarise An Azure Data Factory ARM Template Using T-SQL

  1. Hi Paul,

    Thanks for this blogs which gives detail level of ADF.
    Is there any way to get the information like which child pipeline is a part of which Master pipeline.
    In my case we have 400 plus child pipeline but we do not have any document to identify which are part of which master.

    Please do let me know of there is something we can achieve.
    Thanks

    Like

  2. I’ll see if I can create a PBI and SSRS report off the output and publish. Enumerating each data flow step would be even better, but I haven’t checked to see if they are in the ARM yet (in between job interviews and pluralsight courses!).

    Like

Leave a Reply to Vipinkumar Jha Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.