Passing parameters to SQL queries from a generic ADF pipeline

Question

I am trying to create a generic SQL pipeline to query data from a database.

Got a json file for each feed to pass feed specific parameters to pipeline:

Format of json file for "emp" feed is as follows

{ "feed_name":"emp", "query":select * from emp where emp_id=<<emp_id passed from adf pipeline>>, "raw_base_folder":"raw" }

Json file for dept is like : { "feed_name":"dept", "query":select * from dept where deptno > <> and deptno< <>, "raw_base_folder":"raw" }

Is it possible to create a generic pipeline for all queries executed against a single database but with different parameters passed through ADF pipeline?

I tried to use json files as below:

{ "feed_name":"emp", "query":"declare @p_emp_id varchar2(100);set @p_emp_id=@{pipeline,parameters().emp_id};select * from emp where emp_id=@p_emp_id" "raw_base_folder":"raw" }

and tried to use the above query in a lookup activity /script activity but got the error:

Must declare the scalar variable '@'.

It is throwing error near the statement =@{pipeline,parameters().emp_id};.

Is there any workaround for this issue?..

Answer 1

The error is due to wrong syntax: @{pipeline**,**parameters().emp_id};.

Correct syntax : @{pipeline**.**parameters().emp_id};.

Also the expression should be a valid sql query .In the query, you have mentioned the expression in json format, kindly use only the sql query inside the json to run in the database

Answer 2

The error you are facing is because when you are passing the query from Json file to the script or look up activity it will take whole query as a sting including parameter as below:

To work around this, you need to use intermediate set variable to replace the value of parameter with the pipeline parameter.

to replace @{pipeline().parameters.emp_id} with pipline parameter
expression: @replace(activity('Lookup2').output.value[0].query,'@{pipeline().parameters.emp_id}',pipeline().parameters.emp_id)

Now pass this variable in query of script or lookup activity