1

I am trying to run a Pre SQl script before inserting data using ADF. My Pre SQL Script contains data flow parameter. Please note the parameter value below.

parameter1 = one of the column of excel sheet (suppose value is 'XY')

PFB the query

"DELETE FROM ABC WHERE col_name = {$parameter1}" -- Output :: XY

"DELETE FROM ABC WHERE col_name = '{$parameter1}'" -- Output :: XY I checked above using a derived column activity.

When I run below query

"DELETE FROM ABC WHERE col_name ='{$parameter1}'" -- Error : column operands are not allowed in literal expressions

If I try below

concat('DELETE FROM ABC WHERE col_name =',toString('''+ toString(byPath('$parameter1'))+ ''') ) Result I see in Derived Column DELETE FROM ABC WHERE col_name ='XY' But When I add this to Pre SQL script I am seeing below error Column functions are not allowed in constant expressions

Need suggestion on usage of parameters in Pre SQL script with some examples. Appreciate your time and help.

Improve this question
4
  • What is the error? Did you select "custom expression"? You should be fine just using string interpolation. Put the string in double quotes with single quotes around your string value like this: "DELETE FROM ABC WHERE col_name = '{$parameter1}'" Commented Jun 8, 2021 at 17:30
  • Hi @Chinmay Biswal Kindly let me know if you need more information. Commented Jun 9, 2021 at 8:59
  • @MarkKromerMSFT When ever I am concatineting "DELETE FROM ABC WHERE col_name=" with $parameter , I am getting an error saying "column operands are not allowed in literal expressions". When I debugged found that when we concatinate or write simple queries like DELETE FROM ABC WHERE col_name='{$parameter}' it is only the value of parameter1 (XY in this case). The data flow parameter gets its value from one of the column of excel source. Commented Jun 10, 2021 at 5:33
  • Hi @Chinmay Biswal this means that we can't do that. We need to change another way. Commented Jun 10, 2021 at 5:42

1 Answer 1

Reset to default
3

ADF not allow us to pass a Column functions result to the expression. So we need to add a Lookup activity outside the dataflow. Then pass the output value of Lookup activity to your dataflow parameters via expression.

  1. Add the source dataset to your Lookup activity.
    enter image description here This is my output of my Lookup activity. enter image description here

  2. So I pass the value to my dataflow parameter via expression @activity('Lookup1').output.firstRow.name.
    enter image description here

  3. In the sink of my dataflow, I add "DELETE FROM emp WHERE name = '{$parameter1}'" to Pre SQL scripts. ADF will execute DELETE FROM emp WHERE name = 'Tim'. enter image description here

This works well in my side.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks a lot, Harris. This is working fine now. Can you pls help me with one more thing. When the column name has spaces in it, the parameter value is not getting evaluated and throwing error Invalid template. For example activity('Lookup1').output.firstRow.name is working fine, but activity('Lookup1').output.firstRow.first name is throwing error.
It seems that Lookup activity doesn't support spaces in column name. Can you replace space with eg. "_" .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.