none
Dynamic Variable in URL in Azure Data Factory V2

    Question

  • Hi,

    I am working on a project in which i am using Azure Data Factory V2 HTTP Linked Service. It works fine with the complete URL (URL with the file name) to access the remote file. Those files are created everyday with the date in their names (for instance, data-2012-05-02.csv). I have to automate the pipeline execution. As the file to be accessed is changing everyday, i am not being able to automate the execution.I don't know if it's possible to have a solution to use dynamic variable in URL so that the dynamic variable takes the current date of the system and add it in the URL so that the pipeline gets data from file created on that day.

    Looking forward for help/suggestions.

    Thanks.

    Chetan

    Wednesday, August 22, 2018 12:41 PM

Answers

  • Hi ChetanSK,

    Yes, does dynamically passing the current date to relative URL(not base url in linked service) in http dataset meet your requirement? If so, you could reference the following steps:

    1. Set up a daily scheduled trigger for your pipeline;

    2. Specify a pipeline parameter named 'RunDateTime', see the sample json code: 

    "parameters": {
    "RunDateTime": {       
    "type": "String"
                }
            }

    3. Specify a http dataset parameter named 'fileName' and pass the parameter to the relative URL in http dataset, json code is like:

    {

        "name": "HttpFile1",
        "properties": {
    "parameters": {          
    "fileName": {             
    "type": "String"
                }
            },     
    "type": "HttpFile",     
    "typeProperties": {         
    "relativeUrl": {            
    "value": "@dataset().fileName",               
    "type": "Expression"
                },        
    ...
            }
        }
    }

    4. Pass the following value to filename at the pipeline level, which will construct filename with the pattern: "data-YYY-MM-dd.csv"

    @concat('data-', formatDateTime(pipeline().parameters.RunDateTime, 'yyyy-MM-dd'),'csv')

    5. Trigger run the pipeline and pass @trigger().scheduledTime to pipeline parameter RunDateTime

    "parameters": {
       
    "RunDateTime": "@trigger().scheduledTime"
    }

    This link will give you more details.

    Thanks.



    Wednesday, August 22, 2018 2:20 PM

All replies

  • Hi ChetanSK,

    Yes, does dynamically passing the current date to relative URL(not base url in linked service) in http dataset meet your requirement? If so, you could reference the following steps:

    1. Set up a daily scheduled trigger for your pipeline;

    2. Specify a pipeline parameter named 'RunDateTime', see the sample json code: 

    "parameters": {
    "RunDateTime": {       
    "type": "String"
                }
            }

    3. Specify a http dataset parameter named 'fileName' and pass the parameter to the relative URL in http dataset, json code is like:

    {

        "name": "HttpFile1",
        "properties": {
    "parameters": {          
    "fileName": {             
    "type": "String"
                }
            },     
    "type": "HttpFile",     
    "typeProperties": {         
    "relativeUrl": {            
    "value": "@dataset().fileName",               
    "type": "Expression"
                },        
    ...
            }
        }
    }

    4. Pass the following value to filename at the pipeline level, which will construct filename with the pattern: "data-YYY-MM-dd.csv"

    @concat('data-', formatDateTime(pipeline().parameters.RunDateTime, 'yyyy-MM-dd'),'csv')

    5. Trigger run the pipeline and pass @trigger().scheduledTime to pipeline parameter RunDateTime

    "parameters": {
       
    "RunDateTime": "@trigger().scheduledTime"
    }

    This link will give you more details.

    Thanks.



    Wednesday, August 22, 2018 2:20 PM
  • Hi Wang,

    Thank you so much for the detailed information. I went through every steps you have mentioned.However, i am still having error.The error message looks like this:

    Activity DataCopy failed: 'In function 'formatDateTime', the value provided for date time string '@trigger().scheduledTime' was not valid. The datetime string must match ISO 8601 format.

    What may be the cause for this error?

    Thanks,

    Chetan


    • Edited by ChetanSK Thursday, August 23, 2018 8:25 AM
    Thursday, August 23, 2018 8:25 AM
  • Hi,

    Seemed you pass '@concat('data-', formatDateTime(trigger().scheduledTime, 'yyyy-MM-dd'),'csv')' to relative url, right? Please send me your pipeline, trigger and dataset json definition, which will give me more information for trouble shooting offline. You can remove the credentials from the json code. Email: wangzhan@microsoft.com

    Thursday, August 23, 2018 9:05 AM
  • Hi,

    Thank you for the response.

    I have sent you the json definition of my pipeline,dataset and trigger in email.

    Chetan

    Thursday, August 23, 2018 10:27 AM
  • Was this resolved? I am encountering the same error when using @trigger().scheduledTime.

    Thank you very much.

    Monday, September 17, 2018 1:23 PM
  • Yes.It was resolved. The parameter value should be passed while creating the trigger. It worked for me.
    Tuesday, September 25, 2018 10:44 AM
  • Hi Chetan,

    Good to see you solved the issue. Just curious about the reason why it didn't work before, is it because that you clicked Trigger Now not New/Edit to execute the pipeline run? Please ignore me if I'm wrong. :)

    Saturday, September 29, 2018 2:29 AM
  • Hi Wang,

    It didn't work when i passed @trigger().scheduledTime as input while selecting "Trigger Now". There was some string related error. When i did the same with manual input for datetime (for instance,2018-10-01T01:10:00Z), it worked fine.

    Now, it works fine with trigger.

    Monday, October 1, 2018 7:30 AM
  • Great. :)
    Tuesday, October 2, 2018 12:28 AM
  • Hi Wang,

    I am working on a ADF v2 which require to read an JSON API and produce JSON files. The requirements are...

    1. The API doesn't return more than 100 records in output JSON hence the request has to iterate to read full data from API. So how to iterate?

    2. Need to pass multiple fetch/filter criteria through request each time of iteration... say page number, current date etc

    3. Don't want to use any custom activity

    could you please help.

    Ivan
    • Edited by Puspal_d Friday, October 12, 2018 10:24 AM
    Friday, October 12, 2018 10:23 AM
  • Hi Chetan, 

    Did you figured out how it works @trigger().scheduledTime as input while selecting "Trigger Now". I am trying to send the value during my manual run using "Trigger Now" and would like to send the current date and time as the parameter  value. It doesnt work for me. everytime it accepts as string but no real value.

    Thanks in advance if you can reply. 

    Idrees

    Friday, October 19, 2018 4:36 PM
  • Hi,

    Sounds like this requirement is all about requesting API based on different filter/page number. We're working on add Rest connector to support pagination when requesting API call. Please stay tuned.

    Also it isn't related to this thread, please create another one to track. Thanks.

    Tuesday, October 23, 2018 3:01 AM
  • Hi there,

    "Trigger now" is used for manually pipeline execution not scheduled execution, so the scheduled trigger scope parameter "@trigger().scheduledTime" can't be referenced in "Trigger now". You need to pass a real data time to the pipeline parameter value. Click here to better understand the trigger in ADFv2. Thanks.

    Tuesday, October 23, 2018 3:05 AM
  • Hi,

    The input parameter value @trigger().scheduledTime works when you have set a trigger and scheduled a time for its execution. When you want to run the pipeline manually, you have to pass the datetime value in the following format: 2010-10-10T10:10:10.


     


    • Edited by ChetanSK Wednesday, October 24, 2018 12:15 PM
    Tuesday, October 23, 2018 3:07 PM