topic Re: OnPremise Gateway, cannot write to lakehouse in Dataflow

OnPremise Gateway, cannot write to lakehouse

MikaelDavidsson — Wed, 06 Sep 2023 12:21:31 GMT

Hi,

I am setting up an extraction step in my medallion-arcitechure in Fabric where I want to get data from an on-premise SQL Server. I have installed the on premise datagateway (latest release) and I can find the table I want to extract when I set up a new Data flow Gen 2 artifact. I choose my lakehouse (both de destination and data flow gen 2 artifact exsist in the same workspace) and publish the Dataflow.

All the staging artifacts are created (DataflowStagingLakehouse, SQL-endpoint etc.) and the dataflow is published corectly. However, when running the dataflow I get an error saying that the activity "WriteToDatabaseTableFrom_TransformForOutputToDatabaseTableFrom_****_*******" fails (** is for my table name). The only error message I get is this:

"GatewayCannotAccessSqlError Couldn't refresh the entity because of an internal error"

*text in swedish

The data is stored in the DataFlowStagingLakehouse but is never able to arrive in the designated lakehouse. If i remove the destination from the dataflow, the job works fine without the errors.

What I have done so far:

Opened port 1433 for tcp with endpoint *.datawarehouse.pbidedicated.windows.net according to this faq: On-premises data gateway considerations for data destinations in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn
Recreated the workspace and all the artifacts (several times with different names) as this post suggested: Solved: Dataflow Gen 2 fail error message: WriteToDatabase... - Microsoft Fabric Community

Is thera anyone who has any idea what might be the issue?

Re: OnPremise Gateway, cannot write to lakehouse

miguel — Wed, 06 Sep 2023 18:45:15 GMT

I'm not entirely sure we can reach a conclusion with the information provided, but definitely using the latest gateway and making sure that its not a network firewall issue are on top of my mind for things to consider given the information.

I'd highly recommend reaching out to our support team so they can take a closer look at your scenario and try to troubleshoot.

https://support.fabric.microsoft.com/support

Re: OnPremise Gateway, cannot write to lakehouse

henrikj — Tue, 12 Sep 2023 13:25:54 GMT

I get the same message, although I want to write to a WH.

The GW connection is working for Power BI

Data is present in the staging lakehouse

I've tried to wrtite data both with and without staging.

We use Finnish_Swedish_CI_AS as collation and I've tried using both English locale and Swedish locale for the DF.

Re: OnPremise Gateway, cannot write to lakehouse

MarkBurgess — Tue, 19 Sep 2023 13:47:23 GMT

Hi, did you ever get to the bottom of this. I've checked all the things that you mentioned you'd already done and I'm out of ideas! Thanks

Re: OnPremise Gateway, cannot write to lakehouse

Pragati11 — Tue, 19 Sep 2023 14:18:07 GMT

Hi MikaelDavidsson ,

I did exactly same thing few weeks back and I can confirm that the approach I followed on getting on-premises SQL Server data to Lakehouse using Dataflow Gen 2 works. I tried it again few minutes back by bringing and writing a new table to Lakehouse.

I am not sure where things are gone wrong for you.

I wrote a blog about the whole process and configurations I followed to set this process up at my end.

Worth checking this one once. May be you can identify some difference:

https://datavibe.co.uk/getting-on-premises-sql-server-data-to-fabric-lakehouse/

Re: OnPremise Gateway, cannot write to lakehouse

MikaelDavidsson — Wed, 20 Sep 2023 06:08:42 GMT

We have now managed to get the data in to the lakehouse. Greate success!

The information that I got from our hosting partner was that traffic going to pbipneu5-northeurope.pbidedicated.windows.net. was blocked. When this blockade was lifted, it started working.

New rule in firewall:

Protocol: TCP
Endpoint: pbipneu5-northeurope.pbidedicated.windows.net
Port: 1433

pbipneu5-northeurope depends on what region you have chosen.

Hope this helps.

//Mikael

Re: OnPremise Gateway, cannot write to lakehouse

Element115 — Sat, 13 Jan 2024 08:16:09 GMT

I read your blog page and you wrote that using Organizatinal for authentication does not work. However, that's what I used, and the UI picked up the already existing connection to the on-prem DB just fine, populated the DF Power Query UI fine. It's just when it comes to publishing that sometimes the table is created and the data persisted to the lakehouse and sometimes not. When it is, the columns are always lost and replaced by generic names such as Column1, Column2, etc...

I really don't understand why this is not working and even showing inconsistent behavior in failure.

Re: OnPremise Gateway, cannot write to lakehouse

Anonymous — Mon, 12 Aug 2024 12:12:32 GMT

Hi Mikael,

Thanks for sharing your approach! So am I right in assuming i would have to do these changes on the machine where the Data Gateway is installed?

Re: OnPremise Gateway, cannot write to lakehouse

Element115 — Mon, 12 Aug 2024 16:32:55 GMT

No, only in the firewall settings, wherever that happens to be running on your network.