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

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:

CodePudding user response：

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 .

CodePudding user response：

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