Error column operands are not allowed in literal expressions adf

Aditya Raj 286

i am getting this error Column operands are not allowed in literal expressions.

How can i append an expressions with string in dynamic query while creating a source dataset in dataflow.

Dimeji Olayinka 37 Reputation points

2022-03-31T15:57:52.08+00:00

Did you ever get a solution this question? I am facing the same issue.

Accepted answer

Mark Kromer MSFT 1,146 Reputation points

2021-06-16T17:00:28.54+00:00

Make sure you type in expressions inside the expression builder. Otherwise, ADF is interpreting your input as a query string. Inside the Expression Builder, you can just use string interpolation by putting the query and {$parameter} inside double quotes w/o concat: https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-expression-builder#string-interpolation
Please sign in to rate this answer.

2 people found this answer helpful.
Aditya Raj 286 Reputation points

2021-06-17T03:27:45.793+00:00

i tried that as well but i am getting again same error.

]3
Sign in to comment

3 additional answers

Brian Polk 6 Reputation points

2022-02-16T16:37:42.09+00:00

I just had this same exact issue. Check how you are setting your default values on data flow parameters. Could possibly be in other places too. When setting up the input parameters under debug settings, just using literals results in red outline and indicates an issue. I removed the toString('Parameter1') I originally had and ended up leaving everything blank there. On the data flow its self, I set default values for debugging purposes and used the "Parameter1" syntax. When calling the dataflow via a pipeline it forces you to choose between pipeline expression or data flow expression. Originally i had pipeline express with @ hide ('Parameter1') with the Expression checkbox checked. I changed this to data flow expression and used the "Parameter1" syntax and after that, I was finally able to resolve this error. This is a terribly poor and undocumented issue that should be made clearer
Please sign in to rate this answer.

1 person found this answer helpful.
Vishwanath Jangam 1 Reputation point

2022-02-24T18:48:39.19+00:00

Followed the same approach as you suggested but facing the same error, not sure is it working for you

Dimeji Olayinka 37 Reputation points

2022-03-31T15:56:22.41+00:00

Could you please show screenshots of this. It is not clear to me how this would look. Even if you do assign the parameter at the pipeline level the error still happens in the dataset within the actual dataflow

Brian Polk 6 Reputation points

2022-03-31T17:55:59.517+00:00

Here are 3 screen shots to hopefully help illustrate this. First image is from the dataflow parameters tab. Notice the syntax for just having default values as literals enclosed with double quotes.

The second image is from the pipeline that calls the dataflow. Here i'm just passing literals with the hard coded values I was using for debug. This is using the same syntax with the dataflow expression type. Notice with this option the "Expression" checkbox is not available. this is fine for hard coded values but that's probably not practical for real world application

The third image is where i'm passing values from variables defined earlier in the pipeline (but could be any previous task.) Notice that here i'm using the pipeline expression type but I did NOT check the "Expression" box for any of these. Originally I did because it made sense these were expressions here in the pipeline. But that's not how they end up at runtime. The only difference with that box checked was the resulting runtime value from the variable would be wrapped in single quote if unchecked.

Hope this helps!!

188882-capture1.png

Dimeji Olayinka 37 Reputation points

2022-03-31T17:58:51.35+00:00

Thanks, but what does you actual query look like in the source dataset of the dataflow where you are using the parameters?

Brian Polk 6 Reputation points

2022-03-31T19:51:48.54+00:00

Sorry I don't actually have a true query for my source dataset in the data flow. The dataflow I wrote is for dynamically targeting an Azure Table Storage object in a storage account to ingest into our data warehouse. The input parameters control what table to target as the source in Azure and then where to land the data in our warehouse.
Sign in to comment
Manuel Bustamante 42 Reputation points

2022-02-11T15:22:53.21+00:00

Hi all,

I'm getting the same error. Did you manage to solve it?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Manuel Bustamante 42 Reputation points

2022-03-31T21:29:34.27+00:00
@Anonymous this worked for me. Just keep in mind that in the pipeline you must uncheck column expression when you are passing the parameters!!!!!!

Pipeline. this is how I pass the parameters to the data flow:

Dataflow: these are the parameters inside the dataflow:

Dataflow: this is my source sink inside the dataflow:

Dataflow: this is the expression builder of the query I use inside the source sink:
Please sign in to rate this answer.
Dimeji Olayinka 37 Reputation points

2022-04-01T13:27:30.837+00:00

Hello, thank you for this clarification. I have not been running my dataflow in pipeline yet, as I am debugging and previewing my output exclusively in the dataflow. I don't believe there is a column expression box to uncheck when running debugging/data preview in the dataflow so that is why I am getting the column operands error. Are you able to run this query successfully in the dataflow debugging/data preview or does it only work when you run the dataflow through a pipeline?

Manuel Bustamante 42 Reputation points

2022-04-01T13:53:40.577+00:00

Hi. As you can see in the 2nd picture, I have default values in the dataflow parameters. Thanks to that, I can debug/data preview my dataflow so I can work in both ways (debug/through a pipeline),

Dimeji Olayinka 37 Reputation points

2022-04-01T14:24:32.313+00:00

So the dataflow parameters must be a default value? I am passing in a column where I have applied an aggregate function as the parameter value like so. I keep getting the error with my parameter with brackets and quotes around those brackets {$parameter1} "{$paramete1}" .

Anonymous

2023-04-26T06:27:58.83+00:00

Parameters while passing to any flow task inside mapping data flow must be inside quotes
Sign in to comment