Azure Series : Passing a parameter to an Azure Data Factory pipeline from SQLDW

Introduction

I've the following scenario for you: there are all kind of deliveries of information in an information factory and when these arrives, We want to trigger an event and load tables that are dependent on this information. So I have a process and I have a delivery(number). This is a base for loading certain tables for some of the customers.

So I have a SQL DWH (Synapse now), a Blob Storage and an Azure Data Factory configured. I've created a solution in the following manner:

1. Create a file in a blobcontainer with the following format '<process>_<deliverynumber>.trg' from within a stored procedure in Azure SQL Data warehouse.
2. When a file is detected in a blob container an eventtrigger in Azure Data Factory is executed.
3. This runs an Azure Data Factory pipeline.
4. The Azure Data Factory Pipeline will gather the filename and splits the process and delivery information from the filename.
5. This passed to a stored procedure and the data is written in a table (for demo purposes).

This is described in this blogpost.

1. Create a trigger file

The first step is creating a file in the Blob Storage. This is done with the following statement.

IF EXISTS (SELECT * FROM sys.external_tables 
  WHERE object_id = OBJECT_ID('TestHennieCallPipeline') )  
    DROP EXTERNAL TABLE TestHennieCallPipeline 
    
CREATE EXTERNAL TABLE  TestHennieCallPipeline WITH (
    LOCATION = 'TestHennieCallPipeline_123456790.trg',
    DATA_SOURCE = [BLOB_TRIGGER_CONTAINER],
    FILE_FORMAT = [DoublePipeDelimited_FR2_Outbound]
   ) as select 1 as i from [someschema].[sometable] where 1=2;

Excuting this command will create a file in the blobcontainer (and also a directory).

Creating an event trigger

Now, when the file is created an Event trigger in Azure Data Factory is executed. Here is an example of the Event trigger setup.

This trigger has a parameter but it is not possible to create a parameter in the trigger section. This has to be done in the pipeline pane. There is an option to create triggers available for that.

So, choose for Edit/New Trigger and the following window is shown. The pipeline that will respond on the triggerevent 'TestHennie'. Parameters is equal to 1 because I've already created a parameter.

Click on the name, in this case 'TestHennie', and click two times on Continue until the following screen appears. Here you can enter a parameter and I've included a system variable here.

The system variable is needed for retrieving the name of the file.

@triggerBody().fileName

Create a ADF Pipeline

One of the next steps is creating an Azure Data Factory pipeline for this experiment, I have created two activities: a set variable and stored procedure activity.

The first one is for setting values in variables and the other one is for debugging. The stored procedure will insert the data into a table.

Create a parameter in the pipeline

Creating a parameter in the pipeline is the following step in this experiment. I've created a parameter SourceFile. This parameter is used in the trigger.

Create a user variable in the pipeline

Now in order to do some manipulation of the filename that is retrieved and stored in the parameter, I've added an user variable to the pipeline. This will be used in the Set Variable activity.

Set the variable as a parameter to a stored procedure

Then, I've created an expression in the Set Variable Activity. In this actvity it's possible to use different kinds of funtions and these can be mixed together in order to get the desired result.

Next, I've built an expression and it would be nicer if I could have used less functions and perhaps it's possible. But for now, it will do.

@substring(pipeline().parameters.SourceFile, 
    add(indexof(pipeline().parameters.SourceFile,'_'),1),
    sub(indexof(pipeline().parameters.SourceFile,'.'), 
     add(indexof(pipeline().parameters.SourceFile,'_'),1)
    )
    )

Pass the variable as a parameter to a stored procedure

I've created a stored procedure that adds a record to a table. This way it's possible to investigate the values.

Here the simple table and procedure and some scripting

CREATE TABLE _Hennie (
  Deliverynumber  nvarchar(50)
  )

SELECT * FROM  _Hennie

CREATE PROCEDURE TestHennie (@deliverynumber varchar(50))
AS
BEGIN
 INSERT INTO _Hennie (Deliverynumber) VALUES (@deliverynumber)
END


TRUNCATE TABLE  _Hennie

And the result is that the deliverynumber is derived from the file name based on the expression and written in the table.

Thats it!