Concat Azure Data Factory Pipeline parameters in SQL Query

Question

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

Answer 1

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 and use concat() in sql.

create or alter procedure dbo.proc1 @fname nvarchar(32),@lname nvarchar(32)
as
begin
    select concat(firstname,' ',lastname) as employee_name from [dbo].[employees] where employees.firstname=@fname and 
    employees.lastname=@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: