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

Select * from employees where first_name = '@{pipeline().parameters.fname}' and last_name = '@{pipeline().parameters.lname}' But the best way would be to create a stored procedure with input parameters — Nandan
– Nandan, Commented Jul 7, 2022 at 3:45

Rakesh Govindula · Accepted Answer · 2022-07-08 03:27:58Z

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:

Collectives™ on Stack Overflow

Concat Azure Data Factory Pipeline parameters in SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related