CODEWITHSUNDEEP
azure-data-factorydynamicquery
Mous17
Mous17

Reputation: 13

ADF script/Copy activity not replacing parameter value at runtime

I currently struggling with an ADF copy activity which takes a source as a SQL query and load it to a SQL server table. The query to be executed either in copy activity or script is coming from a SQL server table (let say "table1") and a column called "Script1". But I added to the script in the table a pipeline parameter pipeline().parameters.param1. I want this parameter to be replaced by its value during the execution time but the issue is that it's keeping it as a string. My ADF pipeline has 2 activities:

  1. Lookup activity to fetch the script1 value from the sql server table: the value stored in the table is:

    Select * from table1 where Script1 = '@{pipeline().parameters.param1}'

  2. Then use it in the script activity or Copy activity by adding the following dynamic content as a query:

    activity('Lookup1').output.firstRow.Script1

When I run this script, it is not return any value since the parameter statement is considered as a string. If I copy the same string from the table and put it in the "Set Variable" activity and call the variable in a script it is working fine.

Any thought on this please?

Upvotes: 0

Views: 91

Answers (1)

Bhavani
Bhavani

Reputation: 5317

As the value is store in SQL in the form of string while fetching it from lookup it will come as string only ADF will not detect it as dynamic content or parameter, so, you should replace your parameter in output of lookup activity using below expression in script activity:

@replace(activity('Lookup1').output.firstRow.Script1, '@{pipeline().parameters.param1}', pipeline().parameters.param1)

enter image description here

Then you will be able to run the script activity successfully and run the query successfully as shown below:

enter image description here

Upvotes: 0

Related Questions