Use the output of a Script activity as the items in a ForEach activity in Data Factory

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.