Search code examples
sql-serverazurestored-proceduresazure-data-factorypipeline

Is it Possible to Call an Azure Pipeline from SQL Server

Similar to 'sp_start_job', is there a way to execute a Data Factory Pipeline from SQL Server via a stored procedure?

I tried researching and everything seemed to come back to using stored procedures within Azure Data Factory. I'm familiar with that, but I want to execute it from the SQL Server environment.

It might not be possible. Another option could be to use a trigger based on data within SQL Server, but I'd rather not go that route.

Solution

  • You can use the Pipeline Create run REST API to call the ADF pipeline from SQL script.

    POST https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/pipelines/{pipelineName}/createRun?api-version=2018-06-01

    To call the REST API from SQL, use the OLE automation procedures sp_OACreate, sp_OAMethod which allows us to call the API in SQL server instance.

    Generate the Authentication token for the above API using Service principal. Use that token in calling the REST API with POST method. Give your credentials like subscription id, resource group name, Data factory name and pipeline name in it.

    You can go through this blog by @Chandni Lakhani to understand about the OLE automation procedures.