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:

As an example:-

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

Hi Rose Mary Job ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As per my understanding, you are trying to make the expression in ADF dynamic so that same pipeline can take care of dealing with multiple tables. Please let me know if that is not the case.

Kindly use the valid expression, instead of @{pipeline,parameters().emp_id} , use @{pipeline().parameters.emp_id}

Additionally , the expression should be a valid sql query as the dataset must be pointing to the sql database . 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 ,

For more details , kindly check the below documentation:

Expressions and functions in Azure Data Factory and Azure Synapse Analytics

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou.