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
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.