Extract Data on fly from dynamic API call using ADF

Question

Extract Data on fly from dynamic API call using ADF

Abhijit Bhadra 91

I have some numbers stored as JSON file into a blob storage container, need to read those numbers one by one and then dynamically call APIGW iterating with those numbers.
I have designed a pipeline with "LOOKUP" activity to read those numbers and after "LOOKUP" activity planted "FOR EACH" activity for iteration.
Now i need to extract some values from the API response on fly(don't want to store all values from API and then extract the values) and store as single CSV file.
APIGW response in XML format.

Is there any possibility to do that using ADF?

GP-2266 31 Reputation points

2022-06-09T17:17:04.737+00:00

Yes you can create a dataset for the API you are hitting using the REST linked service: https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#linked-service-properties

Then use this dataset in the copy activity as a source and sink the value to the blob.
Abhijit Bhadra 91 Reputation points

2022-06-10T05:33:54.733+00:00

I am using HTTP linked service with XML Dataset(as source), REST Linked service not applicable for my design as API response in XML format and i am getting error with REST linked service with REST Dataset.
Another thing i don't want to store entire data from API call, my requirement is call the APIGW dynamically, extract required data from there and stored as CSV file.
Data should be appended to the single CSV file for each numbers.

1 answer

Your answer

GP-2266 31 Reputation points

2022-06-09T17:17:04.737+00:00

Yes you can create a dataset for the API you are hitting using the REST linked service: https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#linked-service-properties

Then use this dataset in the copy activity as a source and sink the value to the blob.
Abhijit Bhadra 91 Reputation points

2022-06-10T05:33:54.733+00:00

I am using HTTP linked service with XML Dataset(as source), REST Linked service not applicable for my design as API response in XML format and i am getting error with REST linked service with REST Dataset.
Another thing i don't want to store entire data from API call, my requirement is call the APIGW dynamically, extract required data from there and stored as CSV file.
Data should be appended to the single CSV file for each numbers.

Answer 1

Hi @Abhijit Bhadra ,

Thank you for posting query in Microsoft Q&A Platform.

Yes your approach is right. Use Lookup activity to get dynamical numbers and use ForEach activity to iterate over them. Inside Foreach activity use Copy activity. In Copy activity for source tab use REST or HTTP connector type dataset and in Sink tab use dataset of your destination data store. Please note, Here your source dataset should be parameterized so that dynamically it can take numbers from ForEach iterator and form the API URL dynamically and get response accordingly.

When you say you want to extract required data that means loading data from desired tags itself? If yes, utilize mappings tab under copy activity and import schemas there and remove the mappings of not required tags.

Please check below video where almost similar kind of requirement of forming API URL dynamically inside ForEach loop explained.
https://www.youtube.com/watch?v=EbURwy2a8dM

Below video helps to understand about parameterizing datasets.
https://www.youtube.com/watch?v=9XSJih4k-l8

Hope this helps. Please let us know if any further queries.

-------
Please consider hitting Accept Answer button. Accepted answers help community as well.

ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator

2022-06-27T04:27:42.623+00:00

Hi @Abhijit Bhadra ,

Just checking in to see if the above answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Share via

Extract Data on fly from dynamic API call using ADF

1 answer

Your answer