Forum Discussion
Data Ingestion/ETL/ELT into Fabric
I am evaluating Microsoft Fabric for our data warehouse and overall data strategy. We currently use standard MS SQL Server for our data warehousing and reporting needs and we leverage SSIS to ingest and transform data.
Moving into Fabric, I have experienced some difficulty with automating data ingestion into Fabric. My understanding is we would not be able to load the data directly into a Fabric database or Datawarehouse.
Can someone provide me with the best practice for automating data into Fabric? We have many sources such as Salesforce, Google sheets, vendor SQL databases and other data sources we would like to import into Fabric.
Thanks.
Hi sbalani17 ,
Just to build on what bankmanager already shared — here are a few more options and considerations that might help depending on your source systems and automation needs:
1. REST API Integration
If your sources (like Salesforce or custom apps) expose REST APIs, you can:
- Use Dataflow Gen2 with a custom connector or HTTP connector to pull data.
- Orchestrate API calls using Notebooks (via requests in PySpark or Python) and write directly to Lakehouse.
- For more control, use Azure Functions or Logic Apps to call APIs on a schedule and push data into Fabric via OneLake or Lakehouse endpoints.
2. SSIS Reuse
Since you're already using SSIS:
- You can lift and shift existing SSIS packages into Azure-SSIS Integration Runtime (in Azure Data Factory).
- From there, you can write outputs to Azure Blob or ADLS Gen2, and then use shortcuts in Fabric to access that data without moving it.
3. Google Sheets
- Use Power Automate with the Google Sheets connector to export data to OneDrive or SharePoint, then ingest into Fabric via Data Pipelines.
- Or use a custom Python Notebook to pull data via Google Sheets API and write to Lakehouse.
4. SQL Sources (Vendor DBs)
- Use Data Pipelines with the built-in SQL connectors (ODBC, JDBC).
- If the vendor DB is behind a firewall, consider using a Self-hosted Integration Runtime (like in ADF) to securely connect.
5. Monitoring & Retry Logic
- For production-grade ingestion, make sure to:
- Add error handling in pipelines (try-catch in notebooks or pipeline activities).
- Use logging (e.g., write status to a control table or log file).
- Implement retry policies for transient failures (especially with APIs or cloud sources).
6. Scheduling
- Use Fabric pipeline triggers or Power Automate to schedule jobs.
- For more advanced orchestration, Azure Data Factory still works well and can push data into Fabric via OneLake or Lakehouse endpoints.
Hope this gives you a few more ideas to explore.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
7 Replies
- burakkaragoz
Super User
Hi sbalani17 ,
Here’s a quick breakdown of best practices in Fabric:
Use Data Pipelines (Fabric-native)
- These are the SSIS equivalent in Fabric. You can orchestrate data ingestion from multiple sources like Salesforce, Google Sheets, SQL DBs, etc.
- Pipelines support Copy Data, Dataflow Gen2, and Notebook activities.
Dataflow Gen2
- Ideal for ingesting and transforming data from cloud sources (Salesforce, Google Sheets, etc.).
- You can schedule them or trigger via pipeline.
Lakehouse as Landing Zone
- Instead of loading directly into a Fabric Data Warehouse, use a Lakehouse as your staging area.
- From there, you can transform and load into the DW using Notebooks or T-SQL scripts.
Automation
- Use pipeline triggers (time-based or event-based) to automate the entire flow.
- You can also integrate with Power Automate or Azure Data Factory if needed.
So yes, you can load data into Fabric — just not always directly into the DW. The best practice is to land it in a Lakehouse, then transform/load as needed.
Let me know if you want a sample pipeline setup or a starter template.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Hi burakkaragoz,
Thank you for the information.
Do you know if Data Pipelines or Dataflow Gen2 supports the ability to pull data from REST apis? We have several applications we pull data from via REST/JSON API and each with different authentication methods (oauth 2.0, Bearer token, basic, etc.)
Additionally, do you know if we can connect directly to a Fabric Lakehouse from a third party integration platform such as Fivetran, Matillion or Integrate.io? Just in case the native data tools within Fabric don't support everything we need?
- burakkaragoz
Hi sbalani17 ,
Just to build on what bankmanager already shared — here are a few more options and considerations that might help depending on your source systems and automation needs:
1. REST API Integration
If your sources (like Salesforce or custom apps) expose REST APIs, you can:
- Use Dataflow Gen2 with a custom connector or HTTP connector to pull data.
- Orchestrate API calls using Notebooks (via requests in PySpark or Python) and write directly to Lakehouse.
- For more control, use Azure Functions or Logic Apps to call APIs on a schedule and push data into Fabric via OneLake or Lakehouse endpoints.
2. SSIS Reuse
Since you're already using SSIS:
- You can lift and shift existing SSIS packages into Azure-SSIS Integration Runtime (in Azure Data Factory).
- From there, you can write outputs to Azure Blob or ADLS Gen2, and then use shortcuts in Fabric to access that data without moving it.
3. Google Sheets
- Use Power Automate with the Google Sheets connector to export data to OneDrive or SharePoint, then ingest into Fabric via Data Pipelines.
- Or use a custom Python Notebook to pull data via Google Sheets API and write to Lakehouse.
4. SQL Sources (Vendor DBs)
- Use Data Pipelines with the built-in SQL connectors (ODBC, JDBC).
- If the vendor DB is behind a firewall, consider using a Self-hosted Integration Runtime (like in ADF) to securely connect.
5. Monitoring & Retry Logic
- For production-grade ingestion, make sure to:
- Add error handling in pipelines (try-catch in notebooks or pipeline activities).
- Use logging (e.g., write status to a control table or log file).
- Implement retry policies for transient failures (especially with APIs or cloud sources).
6. Scheduling
- Use Fabric pipeline triggers or Power Automate to schedule jobs.
- For more advanced orchestration, Azure Data Factory still works well and can push data into Fabric via OneLake or Lakehouse endpoints.
Hope this gives you a few more ideas to explore.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
v-prasareCommunity Support
sbalani17, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
burakkaragoz, Thanks for your prompt response
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
- v-prasare
@sbalani17, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
- v-prasare
@sbalani17, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
extremely helpful. thank you both
