Forum Discussion
Ingesting on premise data with notebooks
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.
23 Replies
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 🙂
I agree
I want to do my data ingestion using notebooks (and not data factory)
Is it still nog possbile to reference an on premise data gateway from a notebook?
So as of today, there is no way to ingest on premise data using notebook,
The only way is to use data factory and copy activty to ingest on-premise data to a lake house. Am i right?
Hi Anonymous
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.Catching up to this thread. I can use an on-prem DG connection to a Folder in a pipeline, but I can't use a dynamic connection name. When I choose dynamic connection name, it forces me to use one of three types of connections: Lakehouse, Warehouse, and KQL DB. I'd like to see the other connection types added.
you should be able to use and on-prem gateway to build a connection to connect to your local sql server. Why would you want a dynamic connection name?
The connector we use for this can talk to any DB and its tables, we have given it access to on the on prem sql server.
Asking me "why" is not helpful, but the answer is that I want to use different connections in different environments (dev, test, prod)
We ingest data from an on prem MSSQL server by having created a view on the on-prem server that gets us the information we need.
That view is than called from a pipeline, using a gateway, which allows us to enter the data into a "lakehouse raw" table and next we do the ETL from there. We use the sql endpoint for the ETL, but I assume you could also use a notebook to do it.How to ingest from an API that has certificae based authentication using on-prem gateway? For rest api the only authentication method available are anonymous, basic and service principal.
