Looping through URL fabric pipeline

[DevinL]

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?

[v-cboorla-msft · Accepted solution]

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.

[v-cboorla-msft · Accepted solution]

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.

[DevinL]

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!

[v-cboorla-msft]

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.

[DevinL]

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').

[v-cboorla-msft · Accepted solution]

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.

[DevinL]

@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  

[DevinL]

Thank you! I greatly appreciate your help!