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

Pratik Lad · Accepted Answer · 2023-10-11 11:16:46Z

0

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:

enter image description here

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)

enter image description here

Now pass this variable in query of script or lookup activity

enter image description here

answered Oct 11, 2023 at 11:16

Pratik Lad

8,7282 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nandan · Accepted Answer · 2023-10-11 11:52:09Z

0

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

answered Oct 11, 2023 at 11:52

Nandan

5,2252 gold badges12 silver badges28 bronze badges

Collectives™ on Stack Overflow

Passing parameters to SQL queries from a generic ADF pipeline

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related