Azure, Azure Data Factory, Microsoft Technologies

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.

A Script activity in Azure Data Factory with a ForEach activity

When I populate the items property of my ForEach activity, I use the following expression:

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.

   "effectiveIntegrationRuntime":"AutoResolveIntegrationRuntime (East US)",

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.

3 thoughts on “Use the output of a Script activity as the items in a ForEach activity in Data Factory”

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

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s