Loading...

Interpreting Script activity output json with Azure Data Factory\Synapse analytics

Interpreting Script activity output json with Azure Data Factory\Synapse analytics

Script activity in Azure Data Factory\ Synapse analytics is very helpful to run queries against data sources mentioned here in this document. 


When we use two or more queries in the script activity, it is important to understand the output json of script activity to write expressions based on the output in the subsequent activities.

 

Consider the below Pipeline design:

We have two select queries as follows in script activity, and each of which will give a resultSet. 

select top 2 * from tbl_adf;

Select * from tbladf

Screenshot 2024-01-16 at 7.08.26 PM.png

When debugged, it will give output as below. 

 

 

{ "resultSetCount": 2, "recordsAffected": 0, "resultSets": [ { "rowCount": 2, "rows": [ { "Order Date": null, "Order ID": "271735084", "Ship Date": "2020-01-24T00:00:00Z", "Region": "AUSTRALIA AND OCEANIA", "Unit Cost": "524.96", "Total Revenue": "849829.05" }, { "Order Date": null, "Order ID": "252502572", "Ship Date": "2018-12-29T00:00:00Z", "Region": "MIDDLE EAST AND NORTH AFRICA", "Unit Cost": "152.58", "Total Revenue": "1522290.66" } ] }, { "rowCount": 1, "rows": [ { "lastmodified": "2024-01-12T08:29:48.1508043Z" } ] } ]

 

 

 

 

So,  as per line #2, resultSet count =2. It is because, we have two select queries in the script activity.

In case we want to get the Total Revenue value from Row#1, we have to write below expression.

 @activity('Script1_copy1').output.resultSets[0].rows[0]['Total Revenue']

where, resultSets[0]: First select query result

rows[0]: first row in resultSets[0]

 

Same way, if we want to get the Total Revenue value from Row#2, we have to write below expression.

 @activity('Script1_copy1').output.resultSets[0].rows[1]['Total Revenue']

where, resultSets[0]: First select query result

rows[1]: second row in resultSets[0]

 

And, the below expression gets the rowcount from each resultset.

 @activity('Script1_copy1').output.resultSets[0].RowCount

 @activity('Script1_copy1').output.resultSets[1].RowCount

 

So, by understanding the structure of output json, we are able to write expressions to access individual elements of the output of any activity in ADF\Synapse.

 

Published on:

Learn more
Azure Synapse Analytics Blog articles
Azure Synapse Analytics Blog articles

Azure Synapse Analytics Blog articles

Share post:

Related posts

Primer: Output Data Generated with an Azure Automation Runbook to a SharePoint List

The second part of the Azure Automation runbook primer brings us to output, specifically how to create items generated by a runbook in a Share...

4 hours ago

Databricks vs Azure Synapse Analytics: A Comprehensive Comparison for Modern Data Solutions

Table of Contents Introduction Data is at the core of modern business decision-making. As companies increasingly rely on data-driven insights,...

20 hours ago

Primer: How to Use Azure Automation to Run Microsoft Graph PowerShell SDK Scripts

A reader asked why it seems so difficult to use Azure Automation runbooks to process Microsoft 365 data. In fact, it's not so hard, and here's...

1 day ago

Extending Regular Expressions (Regex) Support on Azure SQL Managed Instance (MI)

We are happy to announce the Private Preview of Regular Expressions (Regex) support on Azure SQL Managed Instance (MI). This new feature bring...

1 day ago

Final Days for the MSOnline and AzureAD PowerShell Modules

After many twists and turns since August 2021, the MSOnline module retirement will happen in April 2025. The AzureAD module will then retire i...

7 days ago

Join the Conversation: Call for Proposals for Azure Cosmos DB Conf 2025!

Are you passionate about Azure Cosmos DB? Do you have insights, experiences, or innovations that the developer community would love to hear? N...

7 days ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy