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:
- Create a file in a blobcontainer with the following format '<process>_<deliverynumber>.trg' from within a stored procedure in Azure SQL Data warehouse.
- When a file is detected in a blob container an eventtrigger in Azure Data Factory is executed.
- This runs an Azure Data Factory pipeline.
- The Azure Data Factory Pipeline will gather the filename and splits the process and delivery information from the filename.
- 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.
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.
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
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!
