Forum Discussion
Pipeline Copy Activity unable to copy delta table to local SQL server
Hey there!
So I'm trying to copy a delta table from my Fabric lakehouse to my local SQL Server, and hitting a weird date issue. Back before Fabric updated things, this was a simple 2-minute job - now it's a headache!
My delta table has a ReportDate column as DATE type. The copy activity seems to read it as DATETIME (with timestamp), even though "Preview data" shows it correctly (Checked the schema and it is DATE). This fails with conversion errors. The error is "Exception occurred when converting value '03/07/2022 00:00:00' for column name 'ReportDate' from type 'Date' (precision:, scale:) to type 'DateTime' (precision:255, scale:255)."
AI suggested using a SQL query with CAST(ReportDate AS DATE) in the source. But that gives me a different error about invalid 'LakehouseSql' connection type. Again AI suggested to use SQL Endpoint connect, I Tried creating a SQL endpoint connection, but copy activity won't use it. Just to make sure it is not a connection issue, I removed "ReportDate" run the activity. it successfuly copy the data. So it's definitely this date column causing the issue.
A friend suggested enabling staging - and it actually works! But copying 2.6 million records takes ~30 minutes, which feels way too slow.
Anyone else run into this Fabric date conversion problem? Any better solutions than the slow staging approach?
Thanks for any ideas!
Turra Worku
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.
8 Replies
Hi TWorku,
You’re copying a Delta table from Fabric Lakehouse to a local SQL Server.
The table has a ReportDate column of type DATE.
Copy activity fails because the system interprets it as DATETIME with a timestamp
Alternatives :
1) Use Mapping in Copy Activity
In the Copy Activity’s Mapping tab, explicitly map the source column to the target column:
Source: ReportDate (Date)
Target: ReportDate (Date)
This often forces proper conversion without staging.
2) Use Dataflow / Data Transformation
Instead of straight Copy Activity, create a Dataflow or Wrangling Dataflow to cast the column explicitly:
Hope it can help you!
Best regards,
Antoine
Thank you for your response Antoine. It is mapped correctly as you suggested. it won't work without enabling staging. I did not want to use Dataflow.
Hi 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-factoryHope 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.
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.
