question

AdityaRaj-8613 avatar image
0 Votes"
AdityaRaj-8613 asked DimejiOlayinka-5292 commented

Error column operands are not allowed in literal expressions adf


106236-image.png



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.

azure-data-factory
image.png (66.3 KiB)
· 1
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.

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

0 Votes 0 ·
MarkKromer-2402 avatar image
1 Vote"
MarkKromer-2402 answered AdityaRaj-8613 commented

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://docs.microsoft.com/en-us/azure/data-factory/concepts-data-flow-expression-builder#string-interpolation

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

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

106358-image.png


106359-image.png


![106413-image.png][3]


0 Votes 0 ·
image.png (49.6 KiB)
image.png (40.5 KiB)
image.png (90.1 KiB)
ManuelBustamante-5839 avatar image
0 Votes"
ManuelBustamante-5839 answered

Hi all,

I'm getting the same error. Did you manage to solve it?

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.

BrianPolk-7407 avatar image
1 Vote"
BrianPolk-7407 answered BrianPolk-7407 commented

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 @string('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

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

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

0 Votes 0 ·

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

0 Votes 0 ·
BrianPolk-7407 avatar image BrianPolk-7407 DimejiOlayinka-5292 ·

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.png188883-capture2.png188921-capture3.png


0 Votes 0 ·
capture1.png (26.7 KiB)
capture2.png (19.2 KiB)
capture3.png (22.7 KiB)

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

0 Votes 0 ·
Show more comments
ManuelBustamante-5839 avatar image
0 Votes"
ManuelBustamante-5839 answered DimejiOlayinka-5292 commented

@DimejiOlayinka-0411 this worked for me. Just keep in mind that in the pipeline you must uncheck column expression when you are passing the parameters!!!!!!

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

188919-image.png

  1. Dataflow: these are the parameters inside the dataflow:

188886-image.png

  1. Dataflow: this is my source sink inside the dataflow:

188928-image.png

  1. Dataflow: this is the expression builder of the query I use inside the source sink:

188944-image.png




image.png (35.6 KiB)
image.png (65.2 KiB)
image.png (10.0 KiB)
image.png (78.6 KiB)
· 3
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.

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?

0 Votes 0 ·

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),

0 Votes 0 ·
DimejiOlayinka-5292 avatar image DimejiOlayinka-5292 ManuelBustamante-5839 ·

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}" .189215-pt1.png189216-pt2.png189158-pt3.png


189166-pt4.png189148-pt5.png


0 Votes 0 ·
pt1.png (16.0 KiB)
pt2.png (61.1 KiB)
pt3.png (13.5 KiB)
pt4.png (32.1 KiB)
pt5.png (57.5 KiB)