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

Dataverse: Create Custom Integration To Azure Cosmos DB for PostgreSQL

In a world where integration is common to do. Especially, because clouds is a common term, for sure, there are requests to integrate Dataverse...

10 hours ago

Recovery options for Azure Virtual Machines (VM) affected by CrowdStrike Falcon agent

We are aware of an issue that started on 19 July 2024 at 04:09UTC, which resulted in customers experiencing unresponsiveness and startup failu...

16 hours ago

Use cases of Advanced Network Observability for your Azure Kubernetes Service clusters

Introduction  Advanced Network Observability is the inaugural feature of the Advanced Container Networking Services (ACNS) suite bringing...

1 day ago

Azure Update Manager to support CIS hardened images among other images

What’s coming in by end of July 2024: Azure Update Manager will add support for 35 CIS hardened images. This is the first time that Update Man...

1 day ago

Mastering your cloud journey: Essentials to Innovating, Migrating and Modernizing, on Azure

We are living during a time of rapid growth in AI technologies and seeing cloud complexity increase as a result of those advanced workloads, w...

2 days ago

Announcing the stable release of Azure Event Grid Namespaces HTTP client libraries

This post announces stable release of the HTTP Azure Event Grid Namespaces client libraries in .NET, Java, JavaScript, Python, and Go. The pos...

2 days ago

Portal extension for Azure Firewall with DDoS protection

Introduction In the ever-evolving landscape of network security, Azure Firewall has emerged as a key player. As a managed, cloud-based network...

2 days ago

Generative AI with Azure Cosmos DB

Leverage Azure Cosmos DB for generative AI workloads for automatic scalability, low latency, and global distribution to handle massive data vo...

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