Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

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.

 

 

Need Help

5 Replies

  • WishAskedSooner's avatar
    WishAskedSooner
    Icon for Continued Contributor rankContinued Contributor
    2 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.  

  • frithjof_v's avatar
    frithjof_v
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.  😁

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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... 🙄