Forum Discussion
Pipeline Copy Activity unable to copy delta table to local SQL server
Hi TWorku,
Thank you for the clarification that makes complete sense, and your approach is valid.
What you are seeing aligns with current Fabric behaviour: when copying directly from a Lakehouse, DATE columns can be exposed as high-precision DateTime metadata at runtime, which causes SQL Server conversion failures. By merging the data into a Warehouse first, the schema is normalized to strict relational types, which is why the subsequent copy to local SQL Server runs successfully and within an expected time frame.
Given your requirement to keep source data types unchanged, this Warehouse-based pattern is a solid and supported design choice. While it does introduce an additional object to manage, it avoids the staging performance penalty and provides predictable copy behaviour. Until the Lakehouse Copy metadata handling is improved, this is one of the most reliable solutions for production workloads.Hope that clarifies. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
Community SupportHi TWorku,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to AntoineW, for those inputs on this thread.
What is happening here is that Fabric is currently exposing DATE columns from the Lakehouse as high-precision DateTime metadata during the copy process, even though the data itself contains only dates. SQL Server rejects that implicit conversion, which explains why removing the column works and why staging succeeds staging normalizes the type before loading. To avoid the slower staging path and keep your earlier performance, you can override the column type just within the Copy Activity: go to Source → Schema and Type, change ReportDate to String, and then ensure in Mapping that the target remains a DATE column. This bypasses Fabric’s incorrect DateTime handling and allows the load to succeed quickly.
Microsoft’s documentation confirms that Copy Activity fully supports manual type conversion and explicit column mapping, including String → Date conversions when default inference fails. You can find these details here:
1. https://learn.microsoft.com/en-us/fabric/data-factory/data-type-conversion-copy-activity
2. https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping?
The Fabric Lakehouse Connector documentation also confirms that Lakehouse → SQL Server is a supported pipeline path, so this workaround aligns with Microsoft best practices.
https://learn.microsoft.com/en-us/azure/data-factory/connector-microsoft-fabric-lakehouse?tabs=data-factory
Hope that clarifies. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
- v-kpoloju-msft
Hi TWorku,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
Good morning v-kpoloju-msft
Thanks for your reply. Our implemented flow is: merge data into a warehouse via a stored procedure, then copy it to the local SQL server. The copy step averages 3 minutes. We are committed to not changing source data types. Yes, this means an extra table and activity to manage, but it's the most suitable solution we have.
- v-kpoloju-msft
Hi TWorku,
Thank you for the clarification that makes complete sense, and your approach is valid.
What you are seeing aligns with current Fabric behaviour: when copying directly from a Lakehouse, DATE columns can be exposed as high-precision DateTime metadata at runtime, which causes SQL Server conversion failures. By merging the data into a Warehouse first, the schema is normalized to strict relational types, which is why the subsequent copy to local SQL Server runs successfully and within an expected time frame.
Given your requirement to keep source data types unchanged, this Warehouse-based pattern is a solid and supported design choice. While it does introduce an additional object to manage, it avoids the staging performance penalty and provides predictable copy behaviour. Until the Lakehouse Copy metadata handling is improved, this is one of the most reliable solutions for production workloads.Hope that clarifies. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
