Event Trigger Azure Data Factory / Synapse Pipeline via Azure SQL Database

Problem Statement :

Is it possible to Trigger Azure Data Factory / Synapse Pipeline on any event occurrence in Azure SQL Database.

Prerequisites :

1. Azure Data Factory / Synapse
2. Azure SQL Database
3. Azure Function ( In this blog, we would be leveraging PowerShell Core Runtime Stack )

Solution :

Note : We would be leveraging Azure SQL Database External REST Endpoints Integration via which we would be triggering an Azure function which in turn would trigger an Azure Data Factory / Synapse Pipeline.

1. Assuming we have a PowerShell Core Runtime Stack Function App set up, update the requirements.psd1 ( present within App files section of App Function) to leverage Az modules.

2. Create a Function of HTTP Trigger type and Function Authorization Level

Enable Managed Identity for the Azure function, which we would need further to provide it access on the needed Azure Data Factory / Synapse.

Add the below code (to Trigger Pipelines) in run.ps1 file within Code + Test Section and Save it.

using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

Connect-AzAccount -Identity

$ResourceGroupNm=$Request.Body.ResourceGroupNm
$ADFNm=$Request.Body.ADFNm
$PipelineNm=$Request.Body.PipelineNm

Invoke-AzDataFactoryV2Pipeline -ResourceGroupName $ResourceGroupNm -DataFactoryName $ADFNm -PipelineName $PipelineNm

# Associate values to output bindings by calling 'Push-OutputBinding'.
Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body = (Get-AzDataFactoryV2Pipeline -ResourceGroupName $ResourceGroupNm -DataFactoryName $ADFNm -Name $PipelineNm) | Select-Object -Property ResourceGroupName,DataFactoryName
})

3. Once the function is created within the Azure function, provide the App function Data Factory Contributor access on the Azure Data Factory whose Pipeline we need to trigger.

4. Login to the Azure SQL database via which we need to trigger the ADF / Synapse Pipeline and execute the queries in the below provided sequence.

a) 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<<>>'

b) 
CREATE DATABASE SCOPED CREDENTIAL [https://<<AzureFunctionName>>.azurewebsites.net/api/<<FunctionName>>]
WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"x-functions-key":"<<FunctionKey>>"}'

Where FunctionKey value is as below :

5. Now for the current use case, the scenario is to trigger an Azure Data Factory / Synapse Pipeline when any data is inserted within a table in the Azure SQL Database.

a) Creating a sample table 

CREATE TABLE dbo.DataSharkX
(
C1 VARCHAR(50),
C2 VARCHAR(50)
)



b) Creating a Stored Procedure which we can leverage within the Database Trigger

CREATE PROCEDURE dbo.DataSharkXSP
AS 
DECLARE @Appurl NVARCHAR(4000) = N'https://<<AzureFunctionName>>.azurewebsites.net/api/<<FunctionName>>';
DECLARE @Apppayload NVARCHAR(max) = N'{
  "ResourceGroupNm": "<<ResourceGroupNm>>",
  "ADFNm": "<<ADFNm>>",
  "PipelineNm": "<<PipelineNm>>"
}';
DECLARE @Appret int, @Appresponse NVARCHAR(max);


EXEC @Appret = sp_invoke_external_rest_endpoint 

    @url = @Appurl,
    @method = 'POST',
    @payload = @Apppayload,
    @credential = [https://<<AzureFunctionName>>.azurewebsites.net/api/<<FunctionName>>],
    @response = @Appresponse output;


SELECT @Appresponse,@Appret



c) Creating a Database Trigger 

CREATE TRIGGER [DataSharkXTrigger]
    ON [dbo].[DataSharkX]
    AFTER INSERT  
    AS
    BEGIN
	EXEC dbo.DataSharkXSP 
    END

Result / Output :

Creating an Event to trigger the Database Trigger ,in turn triggering the Azure Data Factory Pipeline.

INSERT INTO dbo.DataSharkX
Select '1','1'

Response:

{“response”:{“status”:{“http”:{“code”:200,”description”:”OK”}},”headers”:{“Date”:”Fri, 02 Dec 2022 09:58:57 GMT”,”Transfer-Encoding”:”chunked”,”Content-Type”:”application\/json; charset=utf-8″,”Request-Context”:”appId=cid-v1:60cdf84f-fd3b-4f0c-a8f9-8e856a3aee39″}},”result”:{ “ResourceGroupName”: “DataSharkX”, “DataFactoryName”: “DataSharkXV2” }}

Note : To Trigger Synapse pipeline, update the Azure Function with below code

Invoke-AzSynapsePipeline -WorkspaceName <<WorkspaceName>> -PipelineName <<PipelineName>>

Also the API call is asynchronous. Hence, we do not know whether the Data Factory / Synapse Pipeline has actually succeeded. The successful execution of the Stored Procedure doesn’t mean that the Pipeline was a success.