ForEach activity to loop through an SQL parameters table?

Question

I'm working on an ETL pipeline in Azure Synapse.

In the previous version I used an Array set as a parameter of the pipeline and it contained JSON objects. For example:

[{"source":{"table":"Address"},"destination {"filename:"Address.parquet"},"source_system":"SQL","loadtype":"full"}}]

This was later used as the item() and I used a ForEach, switch, ifs and nested pipelines to process all the tables. I just passed down the item parameters to the sub pipelines and it worked fine.

My task is now to create a dedicated SQL pool and a table which stores parameters as columns. The columns are: source_table, destination_file, source_system and loadtype.

Example:

source_table	destination_file	source_system	loadtype
"Address"	"Address.parquet"	"SQL"	"full"

I don't know how to use this table in the ForEach activity and how to process the tables this way since this is not an Array.

What I've done so far: I created the dedicated SQL pool and the following stored procedures:

• create_parameters_table
• insert_parameters
• get_parameters

The get_parameters is an SQL SELECT statement but I don't know how to convert it in a way that could be used in the ForEach activity.

CREATE PROCEDURE get_parameters AS BEGIN SELECT source_table, destination_filename, source_system, load_type FROM parameters END

All these procedures are called in the pipeline as SQL pool stored procedure. I don't know how to loop through the tables. I need to have every row as one table or one object like in the Array.

Answer 1

@Vasas, Monika Hello and welcome to Microsoft Q&A.

I understand you are adjusting your process to move from using pipeline parameter of array type, to using a table or stored proc in dedicated sql pool.

Do not fret. The solution is not so bad. The output of a Lookup Activity results in an array of objects.

Your Lookup Activity should either point to that dedicated sql table, or use the stored proc option. Make sure the "First row only" option is turned off, otherwise things turn out different.

Do be aware there is a limit to the size of the return. 5000 rows or 4MB.

The output may look slightly different from your previous format, it will be like

@{activity('lookupActivity').output.value => the array below

[ {"source_table":"Address", "destination_filename":"Address.parquet", "source_system":"SQL", "load_type":"full"} ]

[https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity#use-the-lookup-activity-result

If this solves your issue, please mark as accepted answer. Let me know if you have more questions on how to use this.