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.
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.
Create a data flow activity that will eventually have 2 parameters (‘start’ and ‘end’ as later created in the data flow activity).
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’
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.
Took me a few days to get it right, but hopefully people find this useful!
CLICK HERE to find out more related problems solutions.