CODEWITHSUNDEEP
azureazure-data-factory
Yaga
Yaga

Reputation: 125

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

I am currently trying to pass a tumbling window trigger's windowStartTime and windowEndTime into the parameters of a data flow pipeline. According to [this forum][1] I can't pass trigger parameters directly into a pipeline, so using [this guide][2] I need to add the following chunk of parameter code into the trigger to pass the two variables into parameters myWindowStart and myWindowEnd in ADF>Manage>Triggers>Trigger code:

{
"name": "trigger5",
"properties": {
    "annotations": [],
    "runtimeState": "Stopped",
    "pipeline": {
        "pipelineReference": {
            "referenceName": "pipeline1",
            "type": "PipelineReference"
        },
        "parameters": {
            "MyWindowStart": {
                "type": "Expression",
                "value": "@{concat('output',formatDateTime(trigger().outputs.windowStartTime,'-dd-MM-yyyy-HH-mm-ss-ffff'))}"
            },
            "MyWindowEnd": {
                "type": "Expression",
                "value": "@{concat('output',formatDateTime(trigger().outputs.windowEndTime,'-dd-MM-yyyy-HH-mm-ss-ffff'))}"
            }
        }
    },
    //redacted
}

However, when pressing 'OK' the code edits do not save. When I reopen the code, none of the edits I make show up.

Any thoughts as to why this is?

Full context: I am trying to pass the tumbling window value into a query in order to do a extract rows from a database depending on when that row was last modified, and upsert those rows into another database.

Upvotes: 1

Views: 1006

Answers (2)

Yaga
Yaga

Reputation: 125

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!

Upvotes: 1

suziki
suziki

Reputation: 14108

Any thoughts as to why this is?

Why your edit was not been saved is because your edit has some error.

Change the code of trigger directly will not show you any error, Failure to save is the feedback from ADF, it told you the edit was wrong.

Try to follow the document and check the error output:

https://learn.microsoft.com/en-us/azure/data-factory/parameters-data-flow

Upvotes: 0

Related Questions