In early 2022, Microsoft released a new activity in Azure Data Factory (ADF) called the Script activity. The Script activity allows you to execute one or more SQL statements and receive zero, one, or multiple result sets as the output. This is an advantage over the stored procedure activity that was already available in ADF, as the stored procedure activity doesn’t support using the result set returned from a query in a downstream activity.
However, when I went to find examples of how to reference those result sets, the documentation was lacking. It currently just says:
“For consuming activity output resultSets in down stream activity please refer to the Lookup activity result documentation.”
Microsoft Learn documentation on the Script activity
Populate the items in the ForEach activity
Similar to a Lookup activity, the Script activity can be used to populate the items in a ForEach activity, but the syntax is a bit different.
Let’s say we have a Script activity followed by a ForEach activity. The Script activity has a single result set.
When I populate the items property of my ForEach activity, I use the following expression: @activity('SCR_ScriptActivity').output.resultSets[0].rows
It starts similar to how we reference output from a Lookup activity. I reference the activity and then the output. But then instead of values I use resultSets[0].rows.
This makes sense when you look at the output from the activity.
{
"resultSetCount":1,
"recordsAffected":0,
"resultSets":[
{
"rowCount":4,
"rows":[
{
"colA":1
},
{
"colA":2
},
{
"colA":3
},
{
"colA":4
}
]
}
],
"outputParameters":{
},
"outputLogs":"",
"outputLogsLocation":"",
"outputTruncated":false,
"effectiveIntegrationRuntime":"AutoResolveIntegrationRuntime (East US)",
"executionDuration":1,
"durationInQueue":{
"integrationRuntimeQueue":1
},
"billingReference":{
"activityType":"PipelineActivity",
"billableDuration":[
{
"meterType":"AzureIR",
"duration":0.016666666666666666,
"unit":"Hours"
}
]
}
}I want the output from the first (in this case, only) result set, so that’s resultSets[0]. The data returned is in the rows array in that result set. So that’s resultSets[0].rows.
How are you liking the script activity in Data Factory? Is there anything else you wish were included in the documentation? Let me know in the comments.
Data Savvy 
Thanks Meagan, this was very helpful. The only additional thing I found was when referencing the current item in an activity I needed to specify the column name from the result set, e.g. @item().colA rather than just @item(), which makes sense in hindsight.
Thanks. Glad it was helpful. Yes, when referring to a specific column inside a ForEach activity, you do use @item().colA. That is true regardless of the input source (Script, Lookup, etc.).
Thank you, Meagan. One thing worth mentioning is that by default this setup will error if the script pulls 0 rows back. You will receive an error similar to the following:
The expression ‘length(activity(”).output.resultSets[0].rows)’ cannot be evaluated because array index ‘0’ cannot be selected from empty array.
One workaround to this is to union a null record to the script and add logic within the foreach loop to do nothing on the null record.