question

MikeWong-1574 avatar image
0 Votes"
MikeWong-1574 asked MikeWong-1574 edited

Azure data Factory - passing in Json array/Object into web connector

Hello,

I'm currently building a pipeline where I am calling a REST API using the web connector and then using the Copy Activity to save the data to a SQL DB. The API URL I want to use has the following structure:

https://api-url.api.com/param?start=0&limit=10&sort=[{"param1": "subparam1", "param2": "subparam2"}]

I can ping this endpoint fine in Python although having trouble building this in the Add Dynamic content section. I am currently trying:

@{concat(
'https://api-url.api.com/param?start',
variable1,
'&limit=',
variable2,
'&sort=[{"param1": "subparam1", "param2": "subparam2"}]'})

although this keeps erroring out. I've tried passing in the last part as follows:

@json(string(&sort=[{"param1": "subparam1", "param2": "subparam2"}])
string(&sort=[{"param1": "subparam1", "param2": "subparam2"}])
&sort=[[{<!-- -->{"param1": "subparam1", "param2": "subparam2"}}]]
&sort=[{""param1"": ""subparam1"", ""param2"": ""subparam2""}]
&sort=[{<!-- -->{"param1": "subparam1", "param2": "subparam2"}}]
&sort=[[{"param1": "subparam1", "param2": "subparam2"}]

However, I keep getting an error. I presume it's because of the way Data Factory is parsing this as a string.

Any explanation or help would be great.

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered MikeWong-1574 edited

Hello @MikeWong-1574 ,
Thanks for the ask and using Microsoft Q&A platform
Can you please this expression and it should work ?

@concat('https://api-url.api.com/param?start=0&limit=10&sort=[{','''''param1''''' ,': ','''''subparam1''''',': ','''''param2''''',': ','''''subparam2''''','}]')

Please do let me know how it goes .
Thanks
Himanshu


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

    • Want a reminder to come back and check responses? Here is how to subscribe to a notification

    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Himanshu,

Unfortunately, this doesn't appear to have worked. I am getting the following error:

Failure happened on 'Source' side. ErrorCode=RestSourceCallFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The HttpStatusCode 400 indicates failure. Request URL: https://api-url.com/param/param_again?start=0&limit=10&sort=[%7B''param1'':''subparam1'',''param2'':''subparam2''%7D] Response payload:<!doctype html><html lang="en"><head><title>HTTP Status 400 – Bad Request</title><style type="text/css">body {font-family:Tahoma,Arial,sans-serif;} h1, h2, h3, b {color:white;background-color:#525D76;} h1 {font-size:22px;} h2 {font-size:16px;} h3 {font-size:14px;} p {font-size:12px;} a {color:black;} .line {height:1px;background-color:#525D76;border:none;}</style></head><body><h1>HTTP Status 400 – Bad Request</h1></body></html>,Source=Microsoft.DataTransfer.ClientLibrary,'

Doesn't look like it's parsing the curly braces as a string and is percent encoding instead?

And another question around param1, subparam1, param2, and subparam2, these look like two ' put together instead of ". Would ADF parse these as two ' or "?

Thank you for your help btw!

0 Votes 0 ·