ETL API Loop with multiple paramaters

[icassiem]

Good day,

I asked the ETL API loop recently where the best method is to via the pipeline task use a python noteback pyspark scipt.

I have some challenges and i dont have a bronze level to store temp values and only a silver and gold:

1. I have reciebved news that i need to use a token which needs to be generated from api as my first task to get access, then need to forward into my python pyspark ingest silver api call, so im not sure how i pass this value between notebooks - actually no clue, i'm new to fabirc+python, i know in ssis i would store the value in a vairable and reuse?

2. Then when i thought i only needed to loop per client, now i believe its up to 3-4 param that i require to loop, so its like i first need to build the combinations and then loop, schu this sounds hectic becuase i need to store those combinations then for each loop and my little F2 is probably goping to bomb out or impact prod?

3. this is hectic becuase the logic lays in the api and i cant access the prod source, so param1 is client, param2 is product, param3 is store (and a client can have 40 stores), so now the loop which i thought was only per cleint has probably 200 loops and thats only for one dataset 😣

4. Is there no way within fabric i use SSIS/data factory?

5. Can i set timeouts and capture api call failure in pipelines to alert for integrity and timeout call to not harm production?

This has now caused doubt in my proposal as the org wont dev api just for reporting 😞

Any ideas or examples please

Regards

[v-kathullac]

Hi  @icassiem  , 

Thanks  @GilbertQ , @rizalard0684 ,for addressing the issue. 

If you're just getting started with notebooks. There are examples that demonstrate how to:

• Authenticate against an API and retrieve an access token.
• Store the token in a variable.
• Use the token in subsequent API requests.
• Perform nested loops (for example, looping through pages, IDs, or child resources) within a single notebook using Python.

  This is a common pattern in Fabric notebooks and is typically implemented using Python libraries such as requests

  Regarding SSIS and Azure Data Factory in Fabric:

  • Fabric does not currently provide a native SSIS runtime that allows you to import and execute existing SSIS packages directly.
  • To run SSIS packages, you would typically need Azure Data Factory's Azure-SSIS Integration Runtime, which is a separate Azure service and incurs additional Azure costs.
  • This capability is not included with an F2 or F4 Fabric capacity.
  • Fabric Data Factory provides Data Pipelines and Dataflows Gen2, but these are not a direct replacement for SSIS. They support many ETL/ELT scenarios but do not execute ,dtsx packages.
  • There is also no local/free version of SSIS that can be imported into Fabric and executed natively.

    If you only have an F2 capacity and no Azure Data Factory or Azure-SSIS Integration Runtime, then your options within Fabric are primarily:

  • Fabric Notebooks (Python, Spark)

  • Fabric Data Pipelines

  • Dataflows Gen2

    For API-based ingestion, a Fabric Notebook is generally the most flexible approach, as it allows you to handle authentication, looping, pagination, and complex API logic within a single notebook.

    If you're new to notebooks, starting with a simple example that retrieves an API token and then calls the API in a loop is a good way to become familiar with the workflow.

     Please let us know if you need any further assistance.

    Thanks,

    Chaithanya.

[GilbertQ]

Hi @icassiem 

From what you are saying it sounds like a notebook is the way to go with regards to this because it can complete all the things you are requesting.

[icassiem]

@v-kathullac , @GilbertQ , @rizalard0684  Thank You so Much for helping, apologines for the delay

I am panicking a little becuase i proposed a new directions "fabric" and not i have an MVP and a POC and this complexity to think of with no python or fabric experience, Hence why i was hoping there was an example or link to share i could learn from fast. Becasue my proosal hangs on a few push backs

[rizalard0684 · Accepted solution]

hey @icassiem I'm trying to understand what you're after and hopefully below answers could help.

1. if your question is "Where I store and reuse a temporary authentication token during pipeline execution" then possible solutions are combining into one notebook (no passing token across multiple notebooks), use Fabric pipeline variables, use Notebook variable Library or even Key Vault (although this is more for secrets, not transient tokens)
2. Re "3-4 loop parameters", yeah it sounds like you're building a nested-loop orchestration, which I doubt that Fabric F2 could handle and potentially you need to increase
3. Not much to comment since this is more like API design to me. Does the API support pagination / bulk requests / multiple stores in one call / all products for a client in one call? I'm exploring if we can reduce from 200 calls to much less
4. Re "Can't I just use Data Factory like SSIS?", It's still preview but yes Fabric will be able to execute SSIS packages using Fabric data pipeline.
   https://community.fabric.microsoft.com/t5/Fabric-Updates-Blog/Invoke-SSIS-Package-Activity-in-Micros...
5. Re setting timeouts and capture api call failures", yes absolutely Fabric data pipeline can configure a timeout so a hung API call doesn't run forever, support Retry Count and Retry Interval settings and alert when failures happen. See below some links to MS learn to explore further.
   https://github.com/MicrosoftDocs/fabric-docs/blob/main/docs/data-factory/activity-overview.md
   https://community.fabric.microsoft.com/t5/Pipelines/Automatic-Retry-x-times-in-case-of-error/m-p/461...
   https://fgarofalo56.github.io/Suppercharge_Microsoft_Fabric/best-practices/error-handling-monitoring...

Appreciate if you can "Kudos" and/or "Accept as Solution" if this answered your query.

[icassiem]

@GilbertQ , @rizalard0684  Thank You Very much

I am only starting, i have no clue of notebooks - do you have an example of the getting the token from api and then use it to do nested looping within one notebook please?

With SSIS i have knowledge but dont i need to have an enterprise SQL license to have SSIS and execute in Fabric? and its only in preview stage correct? I only have F2 and F4 was declined, to have Azure Data factory SSIS i require license, i was hoping that this moved to fabric and SSIS/Datafactory was available within Fabirc? I asked in another thread if Data flow is the new ssis/data factory, i dont have any other licensing, so this means i cant use ssis/azure data factory in fabric correct or is there a local/pc free ssis i can import to fabric?