Error with DATETIME2 in Copy Activity (CSV to SQL Data Warehouse)

Hey everyone,

I'm running into an issue in Microsoft Fabric's Copy Activity while trying to copy a CSV file into my SQL Data Warehouse. I'm adding a new column with a datetime2 value before the copy operation, but it's throwing an error during the process.

I’ve added the following dynamic content to create the datetime2 column in the Additional Columns section:

u/formatDateTime(convertFromUtc(utcNow(), 'W. Europe Standard Time'), 'yyyy-MM-ddTHH:mm:ss.fffffff')

But when the data is copied, I get this error:

ErrorCode=DWCopyCommandOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message='DataWarehouse' Copy Command operation failed with error ''Column '_DTS' of type 'DATETIME2' is not compatible with external data type 'Parquet physical type: BYTE_ARRAY, logical type: UTF8', please try with 'VARCHAR(8000)'. 

It seems like the datetime2 column in my SQL Data Warehouse isn't compatible with the external data from the CSV file, which is being interpreted as a VARCHAR.

I would rather not change the column to VARCHAR(8000) I have tried using the correct datetime2 format in the dynamic content.

Has anyone faced a similar issue when copying a CSV file into a SQL Data Warehouse using Microsoft Fabric's Copy Activity? Or found a way to properly insert datetime2 values? I’d appreciate any advice or workarounds!

Thanks for your help!

Update: I think is because of the Staging area in the workspace. It might convert the data to a certain data type which the warehouse cant accept in the dateetime2 column, but im not sure?

Does anyone know how I can solve this?