question

GraemeRussell-0724 avatar image
0 Votes"
GraemeRussell-0724 asked ShaikMaheer-MSFT commented

Column functions are not allowed in constant expressions

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
image.png (42.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GraemeRussell-0724 ,

Following up to see if below answer helps you? If yes please consider clicking on Accept answer button(130138-image.png), as this could help other community members looking for similar queries.


0 Votes 0 ·
image.png (4.6 KiB)

Hi @GraemeRussell-0724 ,

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.

0 Votes 0 ·

1 Answer

MarkKromer-MSFT avatar image
1 Vote"
MarkKromer-MSFT answered ShaikMaheer-MSFT commented

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.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

0 Votes 0 ·

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

0 Votes 0 ·

@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?

0 Votes 0 ·

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

  1. Use the script to copy the list of columns

  2. Write a short function to transform into a string array

  3. 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("', '")
0 Votes 0 ·

Hi @GraemeRussell-0724 ,

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

0 Votes 0 ·