0

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

newbie
  • 53
  • 10

1 Answers1

1

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

KarthikBhyresh-MT
  • 4,560
  • 2
  • 5
  • 12
  • Thank you for your answer. It helps but I'm not quite there yet. So by using StartOfMonth we get values for start of that current month. How do I get start and end of month values for previous month? – newbie Jan 25 '22 at 03:02
  • there is no direct function for that, you can try as I have used in example : add days of that month (30 or 31) to the `Start of Month` value. For previous month, use combination of `getPastTime()` and `addDays()` functions – KarthikBhyresh-MT Jan 25 '22 at 03:07
  • 1
    Start of Month: `@startOfMonth(string(getPastTime(1,'Month','o')),'dd-MM-yyyy')` – KarthikBhyresh-MT Jan 25 '22 at 03:14
  • 1
    End of Month: `@addDays(startOfMonth(string(getPastTime(1,'Month','o')),'o'),30,'dd-MM-yyyy')` – KarthikBhyresh-MT Jan 25 '22 at 03:15
  • Thank you! those queries work, but now I cant configure the URL with variables in the Copy Activity. I can Only do that in web activity, which will make the process of transferring the output file really complex. Is it possible to pass the URL in Copy activity somehow? – newbie Jan 25 '22 at 03:28
  • you can use the same URL expression as base url for REST source in COPY activity too – KarthikBhyresh-MT Jan 25 '22 at 03:29
  • see https://learn.microsoft.com/en-us/azure/data-factory/parameterize-linked-services?tabs=data-factory – KarthikBhyresh-MT Jan 25 '22 at 03:31
  • Thank you. Please check my EDIT. I will go through the doc. – newbie Jan 25 '22 at 03:37
  • Use linked service parameters to form URL dynamically and then pass value to these parameters by assigning already calculated variable with dates values in the pipeline. – KarthikBhyresh-MT Jan 25 '22 at 03:47
  • Checkout this [example](https://i.stack.imgur.com/OqIKW.gif) like `@{concat('https://api.instantwebtools.net/v1/',linkedService().newdate)}` – KarthikBhyresh-MT Jan 25 '22 at 03:48
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/241405/discussion-between-newbie-and-karthikbhyresh-mt). – newbie Jan 25 '22 at 22:46