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 Developer Community Blog articles
Azure Developer Community Blog articles

Azure Developer Community Blog articles

Share post:

Related posts

June Patches for Azure DevOps Server

Today we are releasing patches that impact the latest version of our self-hosted product, Azure DevOps Server. We strongly encourage and recom...

3 days ago

BYOM: Using Azure AI Foundry models in Copilot Studio

Copilot Studio gives you a fast, secure way to build conversational agents and deploy them into the Microsoft 365 environment without writing ...

6 days ago

Building a Modern Python API with Azure Cosmos DB: A 5-Part Video Series

I’m excited to share our new video series where I walk through building a production-ready inventory management API using Python, FastAP...

7 days ago

Azure Developer CLI (azd) – June 2025

This post announces the June release of the Azure Developer CLI (`azd`). The post Azure Developer CLI (azd) – June 2025 appeared first o...

8 days ago

Restricting PAT Creation in Azure DevOps Is Now in Preview

As organizations continue to strengthen their security posture, restricting usage of personal access tokens (PATs) has become a critical area ...

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