Triggering Azure Pipeline from on premise SQL Server
In this blog, we are going to Trigger ADF Pipeline whenever there is insert or update operation is performed on on-premise SQL Server.
Steps:
- Create ADF Pipeline.
In this case we have already created Pipeline.
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-29.jpeg)
And below is Dataflow.
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-33.jpeg)
- Create PowerShell Script (for Authentication and Triggering Pipeline).
Below is the code for authentication and triggering of Pipeline, to do so we should have following details: Tenant ID, Application ID ,Client Secret, Subscription ID, Resource group Name, API version and pipeline name.
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-31.jpeg)
- Create Job in SQL and Trigger on Table where Insert update or delete.
- Make sure that SQL server agent is running.
- Create a SQL Job with following Job Step
Enter step name, select type as PowerShell, select account will be used to run PowerShell script and enter the code in command section.
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-30.jpeg)
- Next we will create trigger on the tables
We are creating triggers on the tables on which insert, update, delete operations will be performed.
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-31.png)
- Now whenever we will do insert, update or delete in the table the pipeline will automatically get executed.
We have following data in Accounts table,
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-32.jpeg)
Now we updated currency of Account ID 1,
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-35.jpeg)
the moment we updated the record pipeline gets automatically triggered,
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-34.jpeg)
After some time check status,
![](https://www.cloudfronts.com/wp-content/uploads/2020/04/image-36.jpeg)
Hope above helps!