Forum Discussion

alfBI's avatar
alfBI
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

Problem to import on premise data onto a lakehouse using a DataflowGen2

Hi,

 

I am trying to load data from an on premise SQL Server onto a Fabric lakehouse using a Dataflow Gen2.

 

We have alreday in place a on-premise gateway with the most updated release (November 2023)

 

First strange thing occurs when creating the connection to the dataflow gen2 

 

After clicking here

 

 

I fill the the info to connect to the SQL Sever. I select the table to be imported and when trying to select the lakehouse as a destination process get stuck

 

 

 

 

after clicking next process of connection validation keeps runnning forever

 

until it ends with following error message

 

An exception occurred: Downstream service call to url 'https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails' failed.

 

any idea about what is going wrong here?

 

Many Thx

 

Alfons

 

 

 

 

Dataflows

8 Replies

  • dazhang's avatar
    dazhang
    Icon for Microsoft Employee rankMicrosoft Employee
    2 years ago

    Hi Alfons,

     

    This error message suggests that the dataflow is having trouble communicating with the Fabric endpoint via the on-premises data gateway. Is your on-premises data gateway machine behind a firewall, and, if so, can you check your firewall logs to see if there was a failed connection attempt made to https://api.powerbi.com/powerbi/globalservice/v201606/clusterdetails?

     

    Thanks,

    David

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    2 years ago

    Hi alfBI 

     

    Thanks for using Microsoft Fabric Community.

     

    As I understand that you are trying to load data from an on premise SQL Server onto a Fabric lakehouse using a Dataflow Gen2.

     

    In scenarios where your data sources are located on-premises, Microsoft provides the On-Premises Data Gateway to securely bridge the gap between your on-premises environment and the cloud. This document will guide you through the process of accessing on-premises data sources within Data Factory for Microsoft Fabric using the On-Premises Data Gateway.

     

    For more details please refer to the below mentioned links:

    Install an on-premises data gateway.

    How to access on-premises data sources in Data Factory for Microsoft Fabric.

    On-premises data gateway considerations for data destinations in Dataflow Gen2.

     

    I hope this information is helpful. Please do let us know if you have any queries.

  • alfBI's avatar
    alfBI
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Hi,

     

    Effectively there was a problem on the config file "Microsoft.Mashup.Container.NetFX45.exe" never noticed before until now (we have started to use the gateway to load dataflows).

     

    Once fixed we are finally to connect lakehouse to destination, (but with lot of patience, the message below "Validating Connection" appeared during more than 2 minutes....

     

     

    once completed, navigation across workspaces to reach the lakehouse was extremely slow and once the lakehouse was selected it took a long time to create the table (> 2 min).

     

     

    So, more than 5 min only to have the dataflow ready......Finally despite the fact that the dataflow was published following error does appear when executed

     

     

     

    What are we doing wrong here? Access to  *.datawarehouse.pbidedicated.windows.net is already enable on our firewall...... Sounds we are each time closer but not enough 🙂

     

     

    Many Thx

    • dazhang's avatar
      dazhang
      Icon for Microsoft Employee rankMicrosoft Employee
      2 years ago

      Hi Alfons,

       

      "The value is not updateable" is an issue that we just published a fix for today, would you mind upgrading to the latest version of the on-premises data gateway (3000.198.17) and try refreshing your dataflow again?

       

      Thanks,

      David

  • daamruth's avatar
    daamruth
    Icon for Helper II rankHelper II
    2 years ago

    Hi there, 

     

    we had the same problem and were only able to resolve it by not only allowing *.datawarehouse.pbidedicated.windows.net:1433 on the firewall but more (random?!) IPs.

     

    After allowing *.datawarehouse.pbidedicated.windows.net:1433 was not enough, we opened *:1433 and found that the following IP adresses were also adresseed during a load: 

     

    20.38.86.50
    51.107.48.217
    51.107.48.222

     

    They are not able to be assigned to a FQDN. 

    After doing so we are now able to load data from onPrem, via StagingLakehouse into the final lakehouse. 

     

    v-cboorla-msft : What are these IPs? Please update the documentation since only allowing *.datawarehouse.pbidedicated.windows.net:1433 is definetly not enough. 

     

    Thanks

    Daniel 

     

     

  • alfBI's avatar
    alfBI
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    We upgraded to latest gateway release (3000.198.17) and problem remained. Problem was fixed removing the proxy and reviewing the outpbound request. We noticed that por 1433 was not not allowed for *.datawarehouse.pbidedicated.windows.net. Once added everything worked finally

     

    Anyway I have the feeling that documentation about the list of FDQN used on the gateway

     

    https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-communication

     

    should be improved as we have to add additional URLs to the proxy white list

     

    * .analysis.windows.net

    *. applicationinsights.azure.com

    *.data.microsoft.com

    *.dfs.fabric.microsoft.com

     

    Thx for your support

    • dazhang's avatar
      dazhang
      Icon for Microsoft Employee rankMicrosoft Employee
      2 years ago

      Hi alfBI, thanks for the details, sorry for the delay.

       

      For the endpoints you listed:

      • *.analysis.windows.net - this is already included in the documentation
      • *.applicationinsights.azure.com - our documentation includes *.dc.services.visualstudio.com which is for Application Insights, did you already try adding this?
      • *.data.microsoft.com - can you give more details on where you see this is needed?
      • *.dfs.fabric.microsoft.com - we just added this to the documentation, thanks for calling it out.

       

      Thanks,

      David

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    2 years ago

    Hi alfBI 

     

    Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

     

    Thanks