Column functions are not allowed in constant expressions

Graeme Russell 1

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

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2021-09-08T12:26:10.887+00:00

Hi @Graeme Russell ,

Following up to see if below answer helps you? If yes please consider clicking on Accept answer button(), as this could help other community members looking for similar queries.
ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2021-09-21T09:27:05.68+00:00

Hi @Graeme Russell ,

Just checking if below answer helps you? If yes please consider clicking on Accept answer as this could help other community members looking for similar queries.

1 answer

MarkKromer-MSFT 5,186 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.
Please sign in to rate this answer.

2 people found this answer helpful.
Graeme Russell 1 Reputation point

2021-09-08T14:44:36.837+00:00

Thank you for your explanation. In that case, the question becomes: how do I build a parameter that dynamically populates a list of column names?

I have been trying to use columnNames() at the pipeline level but it just seems to be pushing it through as a string, resulting in the same error.

So is something like a get meta data activity is needed?

Mark Kromer MSFT 1,146 Reputation points

2021-09-08T16:12:07.727+00:00

Yes, build a list of column names in the pipeline first using a function like the GetMetadata activity

Graeme Russell 1 Reputation point

2021-09-08T17:03:21.593+00:00

@activity('Get Metadata1').output.structure gets me close, but as I need a strnig array of names only I wanted to try activity('Get Metadata1').output.structure.name but that doesn't work. Is there a notation I missing to get just the names?

Graeme Russell 1 Reputation point

2021-09-09T21:54:31.967+00:00

As a follow-up, I just "hard-coded" this in the end.

Use the script to copy the list of columns

Write a short function to transform into a string array

Paste into sink.key

E.g using R sped things up

readClipboard() %>% str_remove(" as string") %>% str_remove("\t\t") %>% str_remove(",") %>% str_remove("\\{") %>% str_remove("\\}") %>% str_flatten("', '")

ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2021-09-17T12:13:47.973+00:00

Hi @Graeme Russell ,

You can use variables type array in ADF and using Filter activity or ForEach activity also achieve same. Thank you.
Sign in to comment