Forum Discussion

DevinL's avatar
DevinL
Icon for Helper I rankHelper 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 i...
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.

DevinL's avatar
DevinL
Icon for Helper I rankHelper I
2 years ago

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's avatar
v-cboorla-msft
Icon for Microsoft Employee rankMicrosoft Employee
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.

  • DevinL's avatar
    DevinL
    Icon for Helper I rankHelper I
    2 years ago

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

      @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?