Question 291880 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – I have tried with both parameter and variable to change the query string when calling a REST API

I use Azure Data Factory. Here is what I have done.
I have defined two pipeline parameters StartDate and EndDate to be used in query string when I call a rest API.
StartDate as a string with value 2023-11-19 and EndDate as a string with value 2023-11-19.

I have specified the url in Base URl like this
The Base URL = https://api.branas.se/api/bookings/getbookings?skishop=byn%20-%20h%C3%A4mta%20sj%C3%A4lv&pickstatus=all&fromdate=@pipeline().parameters.StartDate&todate=@pipeline().parameters.EndDate to get

As you can see I want to use the value for parameter StartDate as fromdate and the value for EndDate as todate. When I run the pipeline I get a question for StartDate and EndDate and I use the default.
The result is written to a table in sql server but the result is wrong it doesn’t use the query string with parameter that I have specified.

If I instead use this Base URL = https://api.branas.se/api/bookings/getbookings?skishop=byn%20-%20h%C3%A4mta%20sj%C3%A4lv&pickstatus=all&fromdate=2023-11-19&todate=2023-11-24
it work as expected. Here I have hard coded the value for fromdate and todate.

When I get this to work I will use EndDate = @utcnow(‘yyyy-mm-dd’) and
StartDate = @formatDateTime(addDays(utcnow(), -5), ‘yyyy-MM-dd’)

I have tried with both parameters and variables but I can’t make my parameters to take effect when I call the REST API. If I use the ARC(Advanced Rest Client) and specify this URL https://api.branas.se/api/bookings/getbookings?skishop=byn%20-%20h%C3%A4mta%20sj%C3%A4lv&pickstatus=all&fromdate=2023-11-19&todate=2023-11-24

I get the expected result. If I remove this &pickstatus=all&fromdate=2023-11-19&todate=2023-11-24
and call the REST API I get the same result as I do when using Azure Data Factory. I can see that preview data is the same as the data that is written to sql server database table. I have google and tried ChatGPT but haven’t find any solution why it doesn’t work to use parameters or variable to change a query string when calling a REST API.

Many thanks
Tony Johansson

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is how I solved it. I defined two dataset parameter StartDate and EndDate. The base URL was set to this https://api.branas.se/api/bookings/getbookings This is everything before ? which start the query string In Connection for dataset that I called API I set Relative URL to this ?skishop=byn%20-%20h%C3%A4mta%20sj%C3%A4lv&pickstatus=all&fromdate=@{dataset().StartDate}&todate=@{dataset().EndDate} In copy data activity the two dataset parameter StartDate and EndDate is shown. I set StartDate to @formatDateTime(addDays(utcnow(), -4), 'yyyy-MM-dd') and EndDate to @formatDateTime(addHours(utcnow(), 1), 'yyyy-MM-dd') because utc is one hour before Sweden where I live. Now this works as I want


  2. 0

    The issue is you are using pipeline parameters incorrectly in the base URL you need to Enclose the pipeline parameter in curly braces e.g. @{parameter/expression} as below

    The URL should look like below:

    https://api.branas.se/api/bookings/getbookings?skishop=byn%20-%20h%C3%A4mta%20sj%C3%A4lv&pickstatus=all&fromdate=@{pipeline().parameters.StartDate}&todate=@{pipeline().parameters.EndDate}

    enter image description here

    You can use EndDate = @utcnow('yyyy-mm-dd') and StartDate = @formatDateTime(addDays(utcnow(), -5), 'yyyy-MM-dd') as below:

    https://api.branas.se/api/bookings/getbookings?skishop=byn%20-%20h%C3%A4mta%20sj%C3%A4lv&pickstatus=all&fromdate=@{utcnow(‘yyyy-mm-dd’)}&todate=@{formatDateTime(addDays(utcnow(), -5), ‘yyyy-MM-dd’)}

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search