Question 104334 in Azure Question posted in
The official documentation to get you started can be found here.

Azure Data Factory script indexing for pipeline().parameters that are used multiple times in the query

I am using Azure Data Factory script to create parameterized SQL query. I understand that the Index specifies the position in which the parameter’s value should go in the SQL command. However, I don’t know how to handle the situation where pipeline().parameters are used multiple times in the SQL query. In my example below, the RUN_DATE parameter is used twice. When I simply add the same parameters multiple times, it will show "Parameters with duplicate name will be overwritten." Any idea how to solve this?

Here is the query:

@concat('
UPDATE s
SET INSERT_DATE = ''', pipeline().parameters.RUN_DATE,'''',
',UPDATE_DATE = ''', pipeline().parameters.RUN_DATE,'''',
'FROM p 
WHERE p.ID = s.ID
AND s.ID = ''', pipeline().parameters.ID,'''',
';')

See the screenshot:
enter image description here

Tags:

2

Answers


  1. 0

    Not sure if I understand the ask 100% here . The scripts which you shared does use the parameter ID and Run_date and since the parameters are accessible through out the lifetime of the pipeline , so even if you do not pass as script parameter , you script will still work fine .

    Login or Signup to reply.
  2. 0

    • As @Himanshu said, the pipeline parameters can be referred to script directly. I repro’d it with few changes in the script. below is the approach.

    • Pipeline parameters RUN_DATE and ID are taken.

    enter image description here

    • Script activity is taken, and same script is given in the query text box.
    @concat('
UPDATE s
SET INSERT_DATE = ''', pipeline().parameters.RUN_DATE,'''',
',UPDATE_DATE = ''', pipeline().parameters.RUN_DATE,'''',
'FROM p
WHERE p.ID = s.ID
AND s.ID = ''', pipeline().parameters.ID,'''',
';')
    • When pipeline is run in this way, error will not be as said in the question post. But error produced for this script is,

    enter image description here

    • Concat function in adf gives the output value as wrapped within double quotes.
      enter image description here

    • To overcome this, script is modified and given as

    update s
set INSERT_DATE = @{pipeline().parameters.Run_date},
UPDATE_DATE= @{pipeline().parameters.Run_date}
from p
where p.id=s.id and s.id= @{pipeline().parameters.ID}

    enter image description here

    • When pipeline is run, it is executed successfully.
      enter image description here

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search