I’m currently working on an Azure Data Factory pipeline and I’m facing an issue with building dynamic SQL queries within a foreach activity. Here’s the scenario:

I have a lookup activity that retrieves information from an Excel sheet containing “table”, “columns”, and “conditions” data. Following this, I utilize a forEach activity to query the columns from the specified tables based on the provided conditions.

Now, I’m looking to enhance this pipeline by introducing two parameters:

1. An array named “full_load” containing the names of tables that require a full load (no problem with this one).
2. A dictionary-like structure named “incremental_load”, with the keys tableName and dateColumn, for tables that require incremental loads.

Within the copy activity inside the forEach, I aim to construct an expression that:

1. Checks if the current “item().table” exists in any of the “tableName”s of “incremental_load”.
2. If the table exists, I want to incorporate the “dateColumn” from the dictionary into the SQL query.
3. If not, return an empty string.

Is it possible to accomplish this? Or is there a better way to approach the task in hand?

Any help or insights would be greatly appreciated. Thanks in advance!

Here’s the expression Chat GPT gave me:

@if(
    exists(
        pipeline().parameters.incremental_load,
        item().table == '@item().tableName'
    ),
    concat(
        'SELECT ',
        item().columns,
        ' FROM ',
        item().table,
        ' WHERE ',
        item().conditions,
        ' AND ',
        first(
            pipeline().parameters.incremental_load,
            item().table == '@item().tableName'
        ).dateColumn,
        ' = ',
        '''2024-05-12'''
    ),
    ''
)

But it didn’t work (sintaxe problems and whatnot).

I expect to see if item().table is in any tableName of parameters.incremental_load. If it is, then I want to use the corresponding parameters.incremental_load dateColumn in the query.