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

Powering Real-Time Messaging at Scale with Azure Cosmos DB

Microsoft Teams, Copilot, Azure Communication Services and many other product offerings from Microsoft, rely on a unified messaging platform t...

2 days ago

Azure SQL Cryptozoology AI Embeddings Lab Now Available!

Missed out on MS Build 2025? No worries! Our lab is now available for your exploration. Dive into a unique cryptozoology experience using Azur...

3 days ago

Vector Support Public Preview now extended to Azure SQL MI

We are thrilled to announce that Azure SQL Managed Instance now supports Vector type and functions in public preview.  This builds on the mome...

3 days ago

Building Multi-Agent AI Apps in Java with Spring AI and Azure Cosmos DB!

As AI-driven apps become more sophisticated, there’s an increasing need for them to mimic collaborative problem solving – like a t...

3 days ago

What runs ChatGPT, Sora, DeepSeek & Llama on Azure? (feat. Mark Russinovich)

Build and run your AI apps and agents at scale with Azure. Orchestrate multi-agent apps and high-scale inference solutions using open-source a...

4 days ago

Azure Cosmos DB TV – Everything New in Azure Cosmos DB from Microsoft Build 2025

Microsoft Build 2025 brought major innovations to Azure Cosmos DB, and in Episode 105 of Azure Cosmos DB TV, Principal Program Manager Mark Br...

4 days ago

Azure DevOps with GitHub Repositories – Your path to Agentic AI

GitHub Copilot has evolved beyond a coding assistant in the IDE into an agentic teammate – providing actionable feedback on pull requests, fix...

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