ADF Rest API pass dinamically startdate&time and end date time to the API

Question

Hi expert,

I'm able to successfully make a Get call to the API endpoint with the following parameters using ADF Rest API and get the data into the destination table
https://xxxxxxxx.azure-api.net/xxxxx/xxxx/bulk-faults?startDate=2021/09/14&startTime=00:00:00&endDate=2021/09/17&endTime=00:00:00

My question is: how can I remove these parameters from the base url (?startDate=2021/09/14&startTime=00:00:00&endDate=2021/09/17&endTime=00:00:00) and/or have the ADF pipeline utilize the current date time every time I run it? Or how can I replace the date time parameters with the current date time.

please assist,

regards
Ak

Answer 1

Hello @Alan kanyinda and welcome to Microsoft Q&A.

Parameterization is where the relative URL becomes useful.

First give the dataset some parameters, in my example I named them start and end. Later we will use these to hold datetimes, and fill in the URL parameters with them.

Next we break up the URL into a base component and relative component. The base component is set as part of the linked service. The base component would be:
https://xxxxxxxx.azure-api.net/xxxxx/xxxx/bulk-faults?
The relative component is everything which would come after the ?

Third, we use the parameters to construct the relative component

@concat(  
'startDate=',  
formatDateTime(dataset().start,'yyyy/MM/dd'),  
'&startTime=',  
formatDateTime(dataset().start,'hh:mm:ss'),  
'&endDate=',  
formatDateTime(dataset().end,'yyyy/MM/dd'),  
'&endTime=',  
formatDateTime(dataset().end,'hh:mm:ss')  
)  

Lastly in the copy activity, we pass in values for start and end. The function utcnow() gets the current datetime.

{
"name": "RestResource1",
"properties": {
"linkedServiceName": {
"referenceName": "RestService1",
"type": "LinkedServiceReference"
},
"parameters": {
"start": {
"type": "string"
},
"end": {
"type": "string"
}
},
"annotations": [],
"type": "RestResource",
"typeProperties": {
"relativeUrl": {
"value": "@markus.bohland@hotmail.de (\n'startDate=',\nformatDateTime(dataset().start,'yyyy/MM/dd'),\n'&startTime=',\nformatDateTime(dataset().start,'hh:mm:ss'),\n'&endDate=',\nformatDateTime(dataset().end,'yyyy/MM/dd'),\n'&endTime=',\nformatDateTime(dataset().end,'hh:mm:ss')\n)",
"type": "Expression"
}
},
"schema": []
}
}