Forum Discussion

DevinL's avatar
DevinL
Helper I
2 years ago
Solved

Looping through URL fabric pipeline

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?

pipelines
  • v-cboorla-msft's avatar
    v-cboorla-msft
    2 years ago

    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's avatar
    v-cboorla-msft
    2 years ago

    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.

7 Replies

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    2 years ago

    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's avatar
      DevinL
      Helper I
      2 years ago

      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's avatar
        v-cboorla-msft
        Microsoft Employee
        2 years ago

        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.