Categories: Azure Data Factory

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a typical scenario where you retrieve some metadata from a database and then loop over the metadata with a ForEach activity. For example, to copy all the table from a source database in a metadata-driven pipeline. The obvious choice would be to use the Lookup activity. But the Script activity might also be a good choice in certain scenarios. The Microsoft blog post Execute SQL statements using the new ‘Script’ activity in Azure Data Factory and Synapse Pipelines gives a good comparison between the two:

Screenshot taken from the blog post

The biggest advantage for the Lookup is that it supports all data sources, while the Script activity only supports databases (aka stuff that can deal with SQL). The Script activity on the other hand can execute multiple statements which might save you the effort of configuring multiple activities.

The biggest downside of the Lookup is that this the default:

However, there’s also another downside of the Lookup which isn’t mentioned: it can only return max 5000 rows. Do you really want to loop over 5000 items in ADF, probably not. But let’s suppose you do, then the Script activity is your savior.

How do we use it as a Lookup? Configure your query as normal, and make sure there’s at least one SELECT statement in there that actually returns data.

When you run the activity, you’ll get JSON output like this:

You’ll get a count of how many result sets are returned, along with an array called resultSets. Each result set has a row count, and another array called rows. To loop over the rows of a specific result set, you can use the following expression in a ForEach:

@activity('myScriptActivity').output.resultSets[0].rows

If you only have one result set, you take the index 0 of the resultSets array. If you need the second, you use 1, and so on. And that’s it, you are now using a Script activity instead of a Lookup.


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Recent Posts

Techorama 2025 – Slides

You can find the slides for my session Building the €100 data warehouse with the…

1 week ago

Redgate SQL Prompt in SSMS 21

Disclaimer: this post is not sponsored by Redgate :) For those who've missed it, the…

2 weeks ago

Execute Fabric Data Pipeline from Azure Data Factory

In the blog post Call a Fabric REST API from Azure Data Factory I explained…

1 month ago

Azure Data Factory Pipeline Debugging Fails with BadRequest

I recently had a new pipeline fail. It was actually a copy of an old…

2 months ago

Call a Fabric REST API from Azure Data Factory

Suppose you want to call a certain Microsoft Fabric REST API endpoint from Azure Data…

2 months ago

Cool Stuff in Snowflake – Part 14: Asynchronous Execution of SQL Statements

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse).…

3 months ago