MS Fabric Data Flow Gen2. Can I write to an on-premise SQL Server?

[Brian-Peavey]

I have setup a simple DataFlow to copy a table from an on-premise DB2 server.  Within the Power Query interface, I can add a data destination (for copying the data to), but there are only 4 options to select from:

1. Azure SQL database
2. Lakehouse
3. Azure Data Explorer (Kusto)
4. Warehouse

Is it not possible to write data (using a Gen2 DataFlow in Fabric) to an on premise data repository?  Certainly, it makes total sense to land the data in Fabric, but we do have a biz need to also land the data in an on premise repository, too.

Appreciate any comments, suggestions, etc.  Thanks.

[lukiz84]

you can do it, just select Azure SQL Database, enter your local SQL Server and choose the gateway, then the icon will change.

such a bad UI... 🙄

[frithjof_v]

This is not DFG2, but perhaps you can use Data Pipeline copy activity to write data to on-prem SQL Server? I've never tried but it seems to have a SQL Server connector.

[Brian-Peavey]

Thanks.  I did try that (and, yes, you can write to an on premise SQL Server) but ran into another issue where the Gateway version we are at does not support connecting to our DB2 database...whereas a Dataflow Gen2 connection to DB2 is able to use our existing Gateway.  That's a bit frustrating; I'd expect consistency across the Fabric platform when it comes to this connectivity.  😁

[Brian-Peavey]

I can appreciate your frustration 😀.  Colleagues have commented that DataFactory within Fabric is "watered down" in terms of its features/functions...not sure if that comment is valid, as I'm not, by any means, qualified to comment on that.  

BUT...can anyone advise if a Fabric Gen2 Dataflow (or any other mechanism is available in Fabric) that can write data to a "non-Fabric based" data repository; in this case, an on premise SQL Server database?  Thank you.  

[WishAskedSooner]

I will probably get banned for this, but in my opinion, Dataflows Gen2 for Fabric is DOA. While a great idea in principle, DFG2 was either rushed to market or not sufficiently thought out. Consider the fact that users have been complaining from Day 1 that DFG2 objects cannot communicate with Pipeline objects. Microsoft said they would fix this in the H1 2024. Well, here we are in August and nothing yet. With all this said, Notebooks are the only way to really accomplish anything in Pipelines. Will that ever change? Who knows?