topic Re: Problem to import on premise data onto a lakehouse using a DataflowGen2 in Dataflow

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

alfBI — Fri, 10 Nov 2023 13:47:28 GMT

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

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

dazhang — Sat, 11 Nov 2023 05:26:19 GMT

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

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

v-cboorla-msft — Sat, 11 Nov 2023 11:24:10 GMT

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.

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

alfBI — Sun, 12 Nov 2023 19:13:21 GMT

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

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

daamruth — Wed, 15 Nov 2023 12:43:57 GMT

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

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

dazhang — Thu, 16 Nov 2023 03:34:43 GMT

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

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

alfBI — Thu, 16 Nov 2023 13:39:14 GMT

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

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

v-cboorla-msft — Thu, 16 Nov 2023 15:35:03 GMT

Hi alfBI

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

Thanks

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

dazhang — Sun, 04 Feb 2024 20:24:01 GMT

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