Get data from REST API source with iterated nested API calls (with Data Pipelines)

[iBusinessBI]

I have a working Dataflow that does the following:

1. Pulls a Customers list from an API call (https://api.somecrm.com/v1/customers).
2. For EACH Customer it calls another API to get his/hers purchase history (https://api.somecrm.com/v1/purchasehist/{customerid})

3. I have 3 other nested queries, but I'll skip them for simplicity.

I am trying to build the same solution in the new Pipelines in the Fabric. Two reasons for this:

1. Although the Dataflows works fine, it sometimes gets: "429 Too Many Requests error", so I feel that Pipelines will give me more control, such as "Wait" step.

2. I would like to save some outputs as JSON files in a Lakehouse, not just tables.
I am new to this environment so I'd like to learn how can I implement this scenario.

I've seen several documentations and videos, but I am looking for the this specific scenario - several API calls with an iteration...

I tried to build but got stuck in the ForEach activity with a syntax - how do I tell it how to get a specific customerID...

Please help

Thanks

[Ayybee · Accepted solution]

1. First, you need to set up a WEB Activity that pulls the customer list by calling the API/customers.
2. Once you have the customer list, you have to set up a ForEach Activity.
3. In ForEach Activity, go to Settings -> Items -> Add Dynamic Content.   @Array(activity([NAMEOFWEBACTIVITY]).output.data)
4. Here, you need to access the data from JSON. ​
5. Now, add a copy activity inside the ForEach Activity in order to save the JSON files into the lakehouse directly.
6. To access the JSON objects via "customerId" go to the CopyActivity inside ForEach Activity.
7. Source -> Add the connection here.
8. In Realtive URL -> Add dynamic content  @concat('purchaselist/',item().customerId)​
9. Set up the destination where you want to save the JSON objects as JSON files and run the pipeline.

[iBusinessBI]

Please vote for this idea to make pulling data from API sources easier:
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=2bc79710-f776-ee11-a81c-000d3adc66b1

[ajarora]

You should specify the json property name (or path if its nested under a different top level property), which yields the collection of customers.

In general, one good way to debug is by providing a dummy property, and at runtime the failure message will indicate to some extent what properties were available to choose from.

[iBusinessBI]

Exactly my question.

What is the exact syntax for specifying the json path+property (it is nested).

I've googled but couldn't find samples.

Also, sholud I use Web activity for the first call? I saw people use Lookup activity, too.

Which one is the correct?

[HimanshuS-msft]

Hello @iBusinessBI 

Can you please elaborate 
"I tried to build but got stuck in the ForEach activity with a syntax - how do I tell it how to get a specific customerID."?
I am confused as  the FOREACH will iterate all the CustimerID and not any specific one .
Thanks 
Himanshu

[iBusinessBI]

@HimanshuS-msft 
Sure.

Here is my screenshot.

The Web1 activity calls an API that returns JSON with many customers including id and other fields.

For every customerid I need to make another API call.

What do I write in "Items"?

Thanks

[Gregory_Pinard]

Try @activity('Web1').output.value
Then, in your nested activities, you can have a parameter referencing customer id and other fields with this pipeline expression : @item().customerid

[PanuO]

Is it possible to use the @item() properties in mapping? I tried directly use the iteration variable, but I got an error about typing. The UI does not allow manually setting type, so next I tried to create dynamic mapping and define the type there, but then I got an error about invalid JSON (even though JSON is valid). 

"source": {
"path": "@item().id"
},

"JsonException,Message=Array index expected."