How to Pass Parameters to SQL query in Azure Data Factory ADF Tutorial 2021.

 Issue: How to Pass Parameters to SQL query in Azure Data Factory ADF Tutorial 2021.


In this article, we are going to learn how to pass parameters to SQL query in Azure data factory, lets start our demonstration first of all we have to create a pipeline in the azure data factory, let's open your Azure data factory portal and go to the author then go to the pipeline and click on New pipeline, once you click on new pipeline it will open a new window, in this new window, first of all, we have to create two parameters, in my case I have to create two parameters by clicking +New button, which is shown in the picture below. 

 Fig-1: Create parameters for the pipeline in Azure Data Factory.


After creating the parameters click and drag the copy activity in the working window, inside the copy activity go to the source and click on the + new button, select Azure SQL Database, then click and then create a new linked service as shown in the picture below, name this link service, then select Azure subscription, Server name, Database name, Authentication type, User name, Password then test the connection and click on create.



Fig-2: Create linked service for copy activity in azure data factory.


Once our linked service is created, come back to the source, in the source tab we have three options which are, 1. Table, 2. Query, 3. Stored procedure, we will go with the Query click on that and then click on ''add dynamic content [Alt+Shift+D]'' as shown in the picture below. 



Fig-3: Select the pipeline source as a query.


Once we click on ''add dynamic content [Alt+Shift+D]'' it will open a window where you have to insert the query @contact('Select * from dbo.totalsale where Region=''',pipeline().parameters.Region,'''') 
which shown in the picture below.

Fig-4: query for our parameters in Azure data factory.


Once we entered the query click ok and click on debug, once the debug is completed go to the storage and click on the container and then open the output folder, It will show the file,  click on the file and then click on edit and it will show all the records as shown in the picture below, related to our selected parameter which is the region, 


Fig-5: It shows the records as per our executed query. 


The above picture shows the record as per our selected parameter now we are going to select our 2nd parameter which is country, lets write the query and select both parameters,
@contact('Select * from dbo.totalsale where Region=''',pipeline().parameters.Region,'''',' and Country=''',pipeline().parameters.country,'''')  Click on ok then click on debug as shown in the picture below.


Fig-6: Write another query with different parameters.


Once the query is completed successfully go back to the storage and then click on the container and go to the folder and select the file and go to the edit, as you can see in the picture below this time it only chooses the data from Pakistan which is the second parameter.



Fig-7: It shows the records as per our executed query.




Video Demo: How to Pass Parameters to SQL query in Azure Data Factory ADF Tutorial 2021.

















No comments:

Post a Comment