Azure Data Factory Pipeline is getting the SQL query stored in a database table. Eg. Query:

Select * from employees where first_name = 'Sam' and last_name = 'mathew'

I need to update the query in such a way that fname and lname will be a parameter in the azure data factory pipeline say pipeline().parameters.fname and pipeline().parameters.lname

How will the query be modified to use these pipeline parameters in SQL query?

Note: SQL Query is stored in database table. The pipeline will fetch the SQL Query in Copy Data Pipeline Step

CodePudding user response：

You can use the concat function like so:

In Source Query in copy activity:

@concat('select * from employees where first_name= ''', pipeline().parameters.fname,''' and last_name =''',pipeline().parameters.lname,''' ')

CodePudding user response：

As suggested by @Nandan, you have to do this by creating a stored procedure in the database and pass the pipeline parameters in copy activity.

Please follow the demonstration below for that:

• Here I am copying the selected data from query to a csv file in blob. You can copy wherever you want.

• This is my sample SQL table.

• I am creating stored procedure in the database like below with fname and lname as parameters.

    create or alter procedure dbo.proc1 @fname nvarchar(32),@lname nvarchar(32)
    as
    begin
    select * from [dbo].[employees] where employees.first_name=@fname and 
    employees.last_name=@lname;
    end

• Create a pipeline and parameters for fname and lname with default value like below.

• Now create a copy activity and give the SQL table as source and follow below steps.

• Give your sink dataset and my case sink is csv file in blob.
• Now, execute pipeline and give the fname and lname parameter values.

• Click on ok and you can copy the selected data from the query in the database to your sink.

My Output: