CODEWITHSUNDEEP
azure-data-factory
Chinmay Biswal
Chinmay Biswal

Reputation: 393

Sql script used in Azure Data Factory with Parameter not working

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.

Upvotes: 1

Views: 3635

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6043

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.

Upvotes: 3

Related Questions