Column functions are not allowed in constant expressions

Graeme Russell 1 Reputation point
2021-09-07T18:49:27.167+00:00

I have been building data flows that take data from a blob storage (files) into a SQL database. I went to prevent duplicate data from entering the SQL database using UPSERT.

When attempting to use columnNames('StreamName') as a custom expression for key column names for the Sink, ADF returns the titular error.

I have about 15 of these data flows all with different column names, I was hoping I could use this method to avoid having to go through and select each column in each data flow...

130041-image.png

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,875 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MarkKromer-MSFT 5,196 Reputation points Microsoft Employee
    2021-09-08T01:28:31.993+00:00

    ADF requires a constant value there. So you would need to either use a deterministic function in the custom expression (i.e., not columnNames which is dynamic). Or if you want to use a dynamic list of columns, you can use a parameter and set the column list in the data flow activity pipeline parameters. The latter method will work because the column list is constant for each data flow invocation even though you're using a parameter.

    2 people found this answer helpful.