databaserestazure-sql-databaseazure-data-factoryaws-rest-api

Passing date variable dynamically to the URL in ADF

What I am trying to do:

I am fetching data using REST API in ADF, and want to move the output file via Copy Activity to Blob storage. I can successfully do that if I pass in the exact URL with date like this:

"https://xf3txd72ja.execute-api.ap-southeast-2.amazonaws.com/prod/v2/all/01-12-2021/31-12-2021"

The dates in the URL mean the start date and end date. This date changes every month at source and reflects the past 1 month. For example, next months URL will have date like: 01-01-2022/31-1-2022

I want to build a mechanism in my pipeline (if I can) so that I don't have to manually go in and change the date in the URL.

What I have tried so far

I have tried passing Date function in source dataset and also tried passing the date variable.

Date Variable

@formatDateTime(addToTime(convertTimeZone(utcnow(),'UTC','New Zealand Standard Time'),-1,'Month'),'MMM yyyy')

Date Function enter image description here

Error

enter image description here

EDIT

enter image description here

Solution

  • You are missing format specifiers:

    Examples:

    Date format: @getPastTime(1,'Month','dd-MM-yyyy')

    enter image description here

    Start of Month: @startOfMonth(string(utcnow()),'dd-MM-yyyy')

    enter image description here

    End of Month: @addDays(startOfMonth(string(utcnow()),'o'),30,'dd-MM-yyyy')

    enter image description here

    You can use multiple variables and then form a dynamic URL using variables without much confusion.

    WebActivity Configuration

    URL: @Concat('https://xf3txd72ja.execute-api.ap-southeast-2.amazonaws.com/prod/v2/all/',variables('Start of Month'),'/',variables('End of Month'))

    enter image description here

    Refer official MS docs for more details: DateFunctions