Saving trigger parameters in Azure Data Factory to upsert recently modified rows

Figured this out! Posting below for anyone stuck on this as well. This involves passing a system variable to the trigger’s parameter and passings parameters from triggers to a dataset.

The tumbling trigger start and end time must be passed to the trigger parameters, to the pipeline parameters, to the data flow parameters, to the data source’s SQL query, in order to only extract the rows that were updated between the tumbling window’s execution time frame. This upserts recent transactions from a SQL table into a another SQL table.

Pipeline setup:

Create 2 parameters of type string. I named mine MyWindowStart and MyWindowEnd and set the values to ‘@trigger().outputs.MyWindowStart’ and ‘@trigger().outputs.MyWindowEnd’ respectively. This took some guessing to get right. enter image description here

Create a data flow activity that will eventually have 2 parameters (‘start’ and ‘end’ as later created in the data flow activity).

Set the value of ‘start’ and ‘end’ as Pipeline Expression of ‘@pipeline().parameters.MyWindowStart’ and ‘@pipeline().parameters.MyWindowEnd’ respectively. enter image description here

Tumbling trigger setup:

Pipeline>Trigger>New/Edit>New trigger>select Tumbling Window and other details>OK>Trigger Run Parameters MyWindowStart as ‘@trigger().outputs.windowStartTime’ and MyWindowEnd as ‘@trigger().outputs.windowEndTime’ enter image description here

Source data set setup in upsert data flow activity:

I created two parameters of type ‘string’ in the Data flow activity (‘start’ and ‘end’), and set the default value to random date values. Those default values will not be passed into the query if the pipeline sets their value to something else.

Query uses references data flow parameters, and is wrapped in double quotes and the parameter name is encased in ‘{$ -parameter name- }’enter image description here

Took me a few days to get it right, but hopefully people find this useful!

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top