topic Re: Looping through URL fabric pipeline in Pipelines

Looping through URL fabric pipeline

DevinL — Tue, 14 May 2024 20:48:18 GMT

Background: I've pulled all account information from a REST API using a data pipeline and stored it in a datalake table called "Accounts". I now want to pull debit transaction information for each id in the Accounts table and store the data in a new datalake table called "Debits". The URL that I would need to use is something like: //mysource.com/api/accounts/{id}/transactions/debits/

The API doesn't have an option to pull all debits at once, but requires you to replace the {id} with the individual ID's that are now in my Source_Accounts table in the id column.

How does someone go about automating this in Microsoft Fabric?

Re: Looping through URL fabric pipeline

v-cboorla-msft — Tue, 14 May 2024 17:16:20 GMT

Hi DevinL

Thanks for using Microsoft Fabric Community.

I have tried to repro the scenario. Please find the screenshot for reference.

Sample rest API - reqres.in/api/users?page=2

Data in Lakehouse.

Pipeline configuration:

Output:

Try to use the below mentioned code in pipeline expression builder

//mysource.com/api/accounts/@variables('id')/transactions/debits/

If the issue still persists, please do let us know. Glad to help.

I hope this information helps.

Thank you.

Re: Looping through URL fabric pipeline

DevinL — Tue, 14 May 2024 18:36:36 GMT

Thank you for your response!

I've tried to follow your steps but unfortunately there isn't an option to query when selecting a DataLake. I tried doing "Dynamic Content" with "select id from mytablename" and it throws an error stating that my table doesn't exist.

I can select the table's name in a dropdown, but I'm unsure how to only pass the column I need into the ForEach.

Thanks again for taking the time to help me out!

Re: Looping through URL fabric pipeline

v-cboorla-msft — Wed, 15 May 2024 06:25:26 GMT

Hi DevinL

Could you please try to connect your Lakehouse using SQL Endpoint string.

Please follow the below steps.

Copy the SQL connection string for SQL analytics endpoint.

Choose a data source as Azure SQL database and connect using the SQL connection string.

Now you were able to Query the table.

I hope this information helps. Please do let us know if you have any further queries.

Thank you.

Re: Looping through URL fabric pipeline

DevinL — Wed, 15 May 2024 18:52:58 GMT

Thank you again, cboorla.

I'm able to query fine now and the correct values are being passed from the lookup to the ForEach.

I'm unclear at this point what should be in the Set variable 1 within the ForEach. Also, I'm guessing that within the ForEach is where I would put my REST API pull with the variables('id') in the address. I guess I'm confused on how to place the lookup value into the variable('id').

Re: Looping through URL fabric pipeline

v-cboorla-msft — Thu, 16 May 2024 10:50:11 GMT

Hi DevinL

You can set the value - id to variable cust_id using set variable activity.

You can use this variable cust_id in between your relative URL, in order to generate the URL dynamically.

Note : I just use the dummy relative URL in place of that you can replace with actual URL accordingly.

I hope this information helps. Please do let me know in case of any further queries.

Thank you.

Re: Looping through URL fabric pipeline

DevinL — Tue, 21 May 2024 17:14:00 GMT

v-cboorla-msft I've tried using the Web Activity and I get a 404 error. I also tried doing a Copy Data and used the same address //address.com/api/v1/accounts/@variables('cust_id')/transactions/debits and again got a 404 error.

It doesn't show it on the web activity error details, but it does on the copy data details that the variable part of the address is not being replaced.

The error shows the address as:
//address.com/api/v1/accounts/@variables('cust_id')/transactions/debits

When it should show as:
//address.com/api/v1/accounts/1234/transactions/debits

Re: Looping through URL fabric pipeline

DevinL — Tue, 25 Jun 2024 21:40:36 GMT

@v-cboorla-msft I may have figured out a solution, although it seems extremely slow. The address for the API call has to be concatenated with the variable call. @concat('/api/accounts/', variables('cust_id'),'/transactions/debits') was what I used to make it work.

This works, but it takes about 20 seconds per pull. Let's say there are 50,000 records to pull. That would put this at around 11 days to pull all the data. Is there no faster way to do this within a fabric pipeline?