none
@trigger().startTime being passed in as a string when specified as an expression parameter

    Question

  • Goal is to create backups of cosmos db and store them in a blob. Each time the pipeline is triggered a new folder should be created with the folder name being the date the pipeline was triggered.

    Pipeline:

    {
        "name": "backup-stage-va7",
        "properties": {
            "activities": [
                {
                    "name": "ForEach_xe4",
                    "type": "ForEach",
                    "typeProperties": {
                        "items": {
                            "value": "@pipeline().parameters.cw_items",
                            "type": "Expression"
                        },
                        "activities": [
                            {
                                "name": "Copy_xe4",
                                "type": "Copy",
                                "policy": {
                                    "timeout": "7.00:00:00",
                                    "retry": 0,
                                    "retryIntervalInSeconds": 30,
                                    "secureOutput": false,
                                    "secureInput": false
                                },
                                "userProperties": [
                                    {
                                        "name": "Source",
                                        "value": "@{item().source.collectionName}"
                                    },
                                    {
                                        "name": "Destination",
                                        "value": "backup-stage-va7/@{formatDateTime(pipeline().parameters.windowStart,'yy')}@{formatDateTime(pipeline().parameters.windowStart,'MM')}@{formatDateTime(pipeline().parameters.windowStart,'dd')}@{formatDateTime(pipeline().parameters.windowStart,'hh')}@{formatDateTime(pipeline().parameters.windowStart,'mm')}/@{item().destination.fileName}"
                                    }
                                ],
                                "typeProperties": {
                                    "source": {
                                        "type": "DocumentDbCollectionSource",
                                        "nestingSeparator": "."
                                    },
                                    "sink": {
                                        "type": "BlobSink"
                                    },
                                    "enableStaging": false,
                                    "dataIntegrationUnits": 0
                                },
                                "inputs": [
                                    {
                                        "referenceName": "SourceDataset_xe4",
                                        "type": "DatasetReference",
                                        "parameters": {
                                            "cw_collectionName": "@item().source.collectionName"
                                        }
                                    }
                                ],
                                "outputs": [
                                    {
                                        "referenceName": "DestinationDataset_xe4",
                                        "type": "DatasetReference",
                                        "parameters": {
                                            "cw_fileName": "@item().destination.fileName"
                                        }
                                    }
                                ]
                            }
                        ]
                    }
                }
            ],
            "parameters": {
                "windowStart": {
                    "type": "Expression",
                    "defaultValue": "@trigger().startTime"
                },
                "cw_items": {
                    "type": "Array",
                    // redacted
                }
            }
        },
        "type": "Microsoft.DataFactory/factories/pipelines"
    }

    Destination Dataset:

    {
        "name": "DestinationDataset_xe4",
        "properties": {
            "linkedServiceName": {
                "referenceName": "cpx_stage_va7_storage",
                "type": "LinkedServiceReference"
            },
            "parameters": {
                "cw_fileName": {
                    "type": "String"
                }
            },
            "type": "AzureBlob",
            "typeProperties": {
                "format": {
                    "type": "JsonFormat",
                    "filePattern": "arrayOfObjects"
                },
                "fileName": {
                    "value": "@dataset().cw_fileName",
                    "type": "Expression"
                },
                "folderPath": {
                    "value": "backup-stage-va7/@{formatDateTime(pipeline().parameters.windowStart,'yy')}@{formatDateTime(pipeline().parameters.windowStart,'MM')}@{formatDateTime(pipeline().parameters.windowStart,'dd')}@{formatDateTime(pipeline().parameters.windowStart,'hh')}@{formatDateTime(pipeline().parameters.windowStart,'mm')}/@{item().destination.fileName}",
                    "type": "Expression"
                }
            }
        },
        "type": "Microsoft.DataFactory/factories/datasets"
    }

    Error:

    Activity Copy_xe4 failed:  'In function 'formatDateTime', the value provided for date time string '@trigger().startTime' was not valid. The datetime string must match ISO 8601 format.'.
    Per https://docs.microsoft.com/en-us/azure/data-factory/control-flow-system-variables @trigger().startTime is a system variable. Additionally, https://docs.microsoft.com/en-us/azure/data-factory/how-to-read-write-partitioned-data suggests passing in a triggers start time/scheduled time dynamically at runtime. Looking over expressions language wiki the syntax I provided should be valid. Any help would be appreciated.

    Tuesday, August 28, 2018 11:26 PM

Answers

  • Hi,

    I noticed you set "@trigger().startTime" as the default value of the "windowStart", in this way, "@trigger().startTime" will be treated as a string and won't be resolved in run time.

     "parameters": { "windowStart": {
                   
    "type": "Expression",
                   
    "defaultValue": "@trigger().startTime"
               
    },
    }

    As shown in the below pic, you should pass the trigger time to pipeline parameter when trigger pipeline run(not set as default value). Also, the value you pass to "windowStart" isn't right, if you're using tumbling trigger, pass"@trigger().outputs.windowStartTime", if you're using schedule trigger, pass "@trigger().scheduledTime". 

    Thanks.

    • Marked as answer by dmyb Wednesday, August 29, 2018 9:34 PM
    Wednesday, August 29, 2018 2:34 AM

All replies

  • Hi,

    I noticed you set "@trigger().startTime" as the default value of the "windowStart", in this way, "@trigger().startTime" will be treated as a string and won't be resolved in run time.

     "parameters": { "windowStart": {
                   
    "type": "Expression",
                   
    "defaultValue": "@trigger().startTime"
               
    },
    }

    As shown in the below pic, you should pass the trigger time to pipeline parameter when trigger pipeline run(not set as default value). Also, the value you pass to "windowStart" isn't right, if you're using tumbling trigger, pass"@trigger().outputs.windowStartTime", if you're using schedule trigger, pass "@trigger().scheduledTime". 

    Thanks.

    • Marked as answer by dmyb Wednesday, August 29, 2018 9:34 PM
    Wednesday, August 29, 2018 2:34 AM
  • Retried with suggestions - failed with the exact same error.

    Tried: "@trigger().scheduledTime", @trigger().scheduledTime, @@trigger().scheduledTime,  "@@trigger().scheduledTime", @pipeline().TriggerTime, "@pipeline().TriggerTime". Notably @@ doesn't escape the expression - '"@@trigger().scheduledTime"' is invalid for dataFormat... error is returned. String is being passed in instead of expression (even though type of windowStart is listed as expression under pipeline run parameters). Trigger is a schedule trigger, although I'm using manual triggers to test.

    I noticed in your screenshot windowStart is of type string. This does not affect the result. Same error is still experienced.

    Edit:

    Passing in evaluation expressions into manual trigger parameters doesn't expand them - even if the parameters are of type expression. Scheduled trigger works just fine.


    • Edited by dmyb Wednesday, August 29, 2018 9:34 PM
    Wednesday, August 29, 2018 6:43 PM