Ingesting on premise data with notebooks

[egons11]

Hi, I have an on premise database with a running PowerBI gateway and this is how we connected to these sources previously. Now, the requierement is that we would recreate our ETL process in Microsoft Fabric using notebooks and Spark, but currently from what I read it seems like there is no way to do this due to notebooks note being compatible with the on premise gateway. 

If this is the case, are there any other alternatives that I have, other than using Dataflows which are very slow and have performance issues. Ideally I would want to use a work around with Notebooks, that can retrieve this data.
Thanks in advance.

[MattiaColuccia]

Hello,

I'm coming back to this topic to understand if the integration of On-prem gateways in Fabric Spark is on the roadmap for future implementation.

It would be really nice to be able to script ingestion of On-Prem sources (SQL server in my case) through notebooks. Right now I have a complex metadata driven pipeline with multiple steps which are really hard to maintain and could be all replaced by a notebook, if this feature gets implemented.

Thanks 🙂

[egons11]

There are no insights to gain, the functionality doesn't exist, hopefully it's "yet" that it doesn't and will be added soon.

[v-nikhilan-msft]

Hi @egons11 
Thanks for using Fabric Community.
Currently, Microsoft Fabric notebooks do not directly support connecting to on-premise databases through the Power BI Gateway. Fabric Spark doesn’t do the integration with PBI gateway.

As a workaround you can create a Shortcut in Fabric Lakehouse for your external data. This way you can query the data using Notebooks. For more information regarding Shortcuts please refer to this link:
https://learn.microsoft.com/en-us/fabric/onelake/create-onelake-shortcut

Hope this helps. Please let me know if you have any further queries.

[egons11]

How would shortcuts help me in this case? It can externally connect only to S3 and ADLS. The data is stored on local databases.

[v-nikhilan-msft]

Hi @egons11 
Which database are you referring here? Can you please specify?

[egons11]

Multiple local PG instances

[v-nikhilan-msft]

Hi @egons11 
You should use a Data Pipeline to copy the data to lakehouse. Fabric data pipelines will be added to the On Prem data gateway in the next few months. 
What's new and planned for Data Factory in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

If you need something sooner, you should use Azure Data Factory Pipelines with the Self-Hosted Integration Runtime. For more information please refer to these links:
Copy on-premises data using the Azure Copy Data tool - Azure Data Factory | Microsoft Learn
How to ingest data into Fabric using the Azure Data Factory Copy activity - Microsoft Fabric | Micro...

Hope this helps. Please let me know if you have any further queries.

[egons11]

I know about the roadmap, but it's still couple month, we need it now.
In regards to the COPY activity, I am pretty sure you can't use it to build incremental ETL (to append), it re-copies all the data every time you run it, so this is not suitable for us.

[v-nikhilan-msft]

Hi @egons11 
There is no other work around in Microsoft Fabric for ingesting on-premise data. I have shared all the possible options.

[v-nikhilan-msft]

Hi @egons11 

We haven’t heard from you on the last response and was just checking back to see if you got some insights regarding your query. Otherwise, will respond back with the more details and we will try to help.
Thanks

[v-nikhilan-msft]

Hi @egons11 

We haven’t heard from you on the last response and was just checking back to see if you got some insights regarding your query. Otherwise, will respond back with the more details and we will try to help.
Thanks