Forum Discussion
Dataflow Gen2 - Excel in SharePoint to Lakehouse - WriteToDataDestination Error
I have created a Dataflow Gen2 which uses my organisations On-Premises Gateway to connect to a SharePoint Site, navigate to the folder and files and export the excel workbook into Power Query, transform column hears so they're compliant with a Lakehouse's requirements.
All of the Excels are incredibly small sets of data and are only provided some currently semi-static unsystemised information that is required for operating our reporting.
For context:
- We use a gateway to connect to the source (This is at site level, not at file level. Does that matter?)
- The default destination is the lakehouse and update method is set to "Replace" with column mapping set to "Auto mapping and allow for schema change" (though admittedly i do not know how to change this)
- The Dataflow has 5 seperate queries in it to retrieve the excel data from that SharePoint site. I tested it with a single query and still get the same error. I also tested it with a file which has perfectly named columns already and get the same issue.
- Staging is turned off for these files
When I run this Dataflow for the first time it successfully runs, retreiving the information and create the tables in the Lakehouse.
When I refresh this Dataflow/Republish I experience these errors:
- There was a problem refreshing the dataflow: 'Something went wrong, please try again later. If the error persists, please contact support.'. Error code: ActionUserFailure. (Request ID: 77ff67ee-76c1-4dbc-9e22-29758e763e15).
- SP_Excel_ad_narrative_WriteToDataDestination: There was a problem refreshing the dataflow: 'Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Error in comitting version., InnerException: We can't update the value. The operation or its arguments aren't supported by the value., Underlying error: We can't update the value. The operation or its arguments aren't supported by the value. Details: Reason = DataSource.Error;Message = We can't update the value. The operation or its arguments aren't supported by the value.;Detail = #table({"Version", "Published", "Data", "Modified"}, {});Message.Format = We can't update the value. The operation or its arguments aren't supported by the value.;ErrorCode = 10555;Microsoft.Data.Mashup.Error.Context = User GatewayObjectId: 'gatewayobjectidremovedforthispost'. Error code: Mashup Exception Data Source Error. (Request ID: 77ff67ee-76c1-4dbc-9e22-29758e763e15).
I for the life of me cannot understand what the issue is and its driving me mad.
Is there any way to work around this, ingesting the files from SharePoint via a data pipeline or uploading the files on a schedules and then converting them to tables using a notebook. Some way to avoid this nonsense.
Hi JasonArmstrong , Thank you for reaching out to the Microsoft Community Forum.
The issue happens when a Dataflow writes to a Lakehouse table that already exists from a previous run. Even though you've set the update method to Replace, Fabric doesn't actually delete the physical OneLake path, it just tries to overwrite the files. If that path hasn't been fully released yet, which often occurs when Excel files are being ingested through an On-Prem Gateway, you’ll hit the “path already exists” error.
When you write to a staging table instead, you’re usually creating a fresh, isolated target that isn’t reused or referenced elsewhere. That makes it far less likely to be locked or conflicted. And after the Dataflow finishes, you use a Spark notebook to drop and recreate the final table. Spark handles that clean-up at the storage level properly removing the folder and all contents in a way Dataflow doesn’t.
You asked whether you could just drop the final table before running the Dataflow. Technically yes, but in practice it’s unreliable. OneLake can hold onto the storage path momentarily even after deletion and if the Dataflow tries to write before it’s fully cleared, the conflict returns. That’s why the staging pattern works better, the Dataflow runs without touching production and Spark takes care of the overwrite safely afterward. It’s not just moving the problem, it’s solving it by separating responsibilities between tools that handle them differently.
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
10 Replies
- GilbertQ
Super User
What happens if you had to change the destination data flow to simply overwrite? and not to also. set the column tabs to auto and leave them as fixed to see if that first will solve the issue? Then I need to know it's the autumn mapping that is causing the issue
Firstly, thanks for replying and offering your help.
To test what you have suggested i created a new flow to ingest one of the excel files into the lakehouse.
Instead of setting the default data destination I manually set it to the lakehouse so I could use the UI interface to tell the flow to replace the data isntead of appending (it was already replacing in the default).
I was unsure what you would suggest when choosing dynamic vs fixed - I chose fixed as the tooltip for dynamic sounded like it could be an issue downstream if changes were ever made to the Excel (would appreciate some context to this decision too, does dynamic mean if I have measures in my custom semantic model they will be deleted if someone changes a column name in an excel?)
I left the column names as they were e.g. i kept it as "Output Category" istead of changing to "output_category". I was forced to map the datatypes as the default "Any" was not permitted if using the auto option.
The dataflow published successfully the first time, as expected, and then proceeded to refresh successfully. I added a new row to the dataset and it loaded into the lakehouse table successfully.
I created another flow to test the same process with "Dynamic" selected and it published successfully creating the tables in the Lakehouse but failed the refresh. So it seems that is the issue, though I don't understand why.
v-hashadapuCommunity Support
Hi JasonArmstrong , Thank you for reaching out to the Microsoft Community Forum.
This is caused by using Dynamic schema with Auto mapping and allow for schema change, which causes the Dataflow to try and rebuild the Lakehouse table during refresh. This often fails when the Excel structure changes even slightly, especially through an On-Premises Gateway.
Configure the Dataflow to use a Fixed schema and manually map each column. Set explicit data types, avoid leaving any column as “Any”. Also, make sure the update method is set to Replace, not append. This prevents table recreation and ensures stable refreshes, as your testing already confirmed.
If your Excel files are likely to change structure in the future, consider adding a SharePoint landing folder and use a notebook to clean and standardize files before ingestion into the Lakehouse. This gives you control over schema changes without relying on fragile Dynamic settings.
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
