Forum Discussion
Transport-level Error using Dataflows Gen2
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
22 Replies
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-7e2c8dfa4b54I'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.
Agree with everything you said
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.
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.
I upgraded to the same gateway edition and had the same problem before and after. Agree with everything you said.
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.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.
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?
We have already solved for the firewall issue in June/July. I have confirmed we can connect to the sql endpoint just fine.
I’m also experiencing a similar issue when running Dataflows Gen2, but in my case, the data is being moved from a PostgreSQL server (cloud-hosted) to a Lakehouse in Microsoft Fabric. The on-prem component is the gateway connector. If the Lakehouse is open while the Dataflow is refreshing everything works fine, if not I get a similar error:
Mashup Exception Data Source Error: Couldn't refresh the entity because of an issue with the mashup document.
MashupException.Error: Microsoft SQL: 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.)
Details:
Reason = DataSource.Error;
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;
State = 0;
Microsoft.Data.Mashup.Error.Context = System;
GatewayObjectId:
