Transport-level Error using Dataflows Gen2

[AnthonyGenovese]

We are starting to receive the following error when running a dataflow gen 2 to the from on prem SQL Server to the lakehouse.

Mashup Exception Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Error in replacing table's content with new data in a version: #{0}. Details: [DataSourceKind = "SQL", DataSourcePath = "XXX", Message = "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)", ErrorCode = -2146232060, Number = 10054, Class = 20] GatewayObjectId: 0e14aac5-92c6-427b-94de-99eb9fb12b12

[ebjim]

I filed a support ticket a while back and spoke to a rep today who wanted to see the error in action. Trouble is, I have switched to using a Lakehouse as the destination and with other changes (things have to move forward), I could not replicate the error, but she did give me a few things to follow up with. I would encourage anyone who has not filed a ticket to do so. 

[PaulCH]

Bumping this. As part of our support ticket we've also been double and triple checking our firewall, even though as this is happening with data moving exclusively within the Fabric environment it's hard to see how our firewall could be affecting things. As expected - and as others have found - we can ping the full SQL connection string of a warehouse or lakehouse from the server that the PowerBI gateway is installed on, on port 1433 and get successful responses.

Can we move on and look at other potential causes and solutions? This is a pretty show-stopping bug for us; we can ingest data but all our avenues for transforming that data are currently blocked off.

[miguel]

Please continue working with the support team to collect traces and more information required to diagnose and troubleshoot the problem.

[motoray]

I was on a call with Microsoft yesterday and they confirmed this is a known issue and that they're working to resolve it.

[ebjim]

I have every reason to believe that the dataflow-warehouse connnection is still unstable after seeing similar issues, though in my case, everything is located in the same Fabric workspace. The gateway version I am using is 3000.182.5.

- When I set a warehouse as my data destination, the dataflow refresh will fail with the transport level error, but not when I use a lakehouse destination. My source data is coming from another lakehouse in the workspace.

- In my warehouse, I have a table in a schema other dbo. That table no longer appears on the navigation pane even though no one has touched it. When I click to refresh the tables folder, I get an error declaring "we couldn't get your warehouse's updated schema'. But when I try to set the data destination in a dataflow, the wizard shows that table.  

[AnthonyGenovese]

I upgraded to the same gateway edition and had the same problem before and after. Agree with everything you said.

[PaulCH]

I came here to create a post detailing almost the same problem. We are now able to successfully move tables from our On-prem SQL database to the Lakehouse using dataflows, but are getting this issue whenever we either try to move the tables from a Lakehouse to a Warehouse using a dataflow, or when we try to move data from our on-prem SQL database direct to a Warehouse. To slighly add to the mystery, it doesn't happen every time. Just mostly. Time to failure seems to decrease consistently with the row count of the table being moved.

Example error code:
 DataSourceKind = Lakehouse;DataSourcePath = Lakehouse;Message = A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.);ErrorCode = -2146232060;Number = 10054;Class = 20 GatewayObjectId: f563ceac-bba5-4f31-bd45-7e2c8dfa4b54

[motoray]

I've been having the same issue. I have a ticket open with Microsoft and they keep trying to get me to open the already open firewall. If I only try a a handfull of rows--say 100 or so--it works consistently. Sometimes I can get it to transfer a few thousand. Then it will simply stop working...almost like I've run too many tests and it needs a break for a while.

[AnthonyGenovese]

Agree with everything you said

[motoray]

This is finally working--going directly from on-prem SQL to a warehouse--for me. The first time I run to load data to a new table, I have to limit the load to just a few rows or I get the transport-level error. Once the new table is in the warehouse, though, I can load the full 5 million plus row dataset.

[AnthonyGenovese]

What version is your gateway?

[motoray]

3000.186.18

[PaulCH]

Interesting! I had a note from the guys handling our support ticket yesterday to give things another try, but it still resulted in a failure after 6 hours (this is moving a table from a lakehouse to a warehouse in the same workspace).

Also, I'm not sure about anyone else, but we don't get 'transport-level' errors anymore, we now get "There was a problem refreshing the dataflow". Symptoms are the same though.

[motoray]

I haven't tried moving data from Lakehouse to Warehouse. How big is your dataset?

[PaulCH]

2.1 million rows. I think it's probably worth deleting the existing warehouse, creating a new one, doing an initial load of a few rows, then trying the full dataset. Will report back.

[motoray]

That's ultimately what I did once Microsoft let me know they thought they'd fixed the underlying issue--rebuilt the things.

For kicks, I just ran a load from my test lakehouse to my test warehouse and it went OK. It was a 100 row load to start. I'm going to pull the filter off and see whether all 5 million rows will load. Will update here once it completes. 

[motoray]

I was able to load 5,000,000 rows from my Lakehouse to my Fabric Warehouse.

[PaulCH]

I can confirm that Lakehouse > Warehouse is now working for us too. 10 row initial load, followed up by 2.1 million rows. All done in a couple of minutes. Happy days!

[AnthonyGenovese]

Now I just wish I had some time to try this all over again! Can't wait. 

[miguel]

Hi!

This error is typically thrown by SQL Server when there's a firewall issue happening at one point. It would be something between your network and the service itself.

Could you please review the article below and let us know if it helps?

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