TIME Datatype issue while migrating from Synapse to FabricWarehouse

Hi AnmolGan81,

Even though you're applying the CONVERT in the source query of the Copy Activity, the pipeline still tries to infer the schema from the underlying table, not just from the SELECT output especially when working with Synapse and Parquet-based movement. This is why the TIME column is still being interpreted as TIME, causing the same error. Here’s the correct and working approach:

* Use a View to Force the Conversion at the Source Layer Go to Synapse SQL Dedicated Pool and create a view like this:

CREATE VIEW vw_your_table_migration AS
SELECT
col1,
col2,
CONVERT(VARCHAR(12), your_time_column, 114) AS formatted_time
FROM your_original_table;
* In the Fabric Data Pipeline, set your source to this view, not the original table.

* In the Copy Activity, go to the mapping tab and map formatted_time -> VARCHAR(12) column in your Fabric Warehouse.

* If you eventually need to convert it back to TIME(2) at the destination: 

ALTER TABLE your_table ADD converted_time_column TIME(2);
UPDATE your_table
SET converted_time_column = TRY_CAST(formatted_time AS TIME(2));

This works because using a view ensures the data type is enforced at the SQL engine level before the pipeline picks it up. This prevents the pipeline from "seeing" the original TIME type, which avoids the Parquet compatibility issue. Casting inside the Copy Activity query alone doesn’t override the underlying metadata the view helps you bypass that.

 

Best Regards,

Hammad.

 

 

Hi AnmolGan81,

Thanks for the clarification and following up with the conversation and that makes complete sense especially if you're aiming for an automated daily sync process. Modifying the source datatype each time (even virtually through a view or a query cast) isn’t ideal for operational stability.

To answer your question, there’s currently no way to natively migrate TIME(n) from Synapse Dedicated Pool to Fabric Warehouse using Data Pipelines without transforming it, because Fabric pipelines internally use Parquet as the intermediate format. Also parquet doesn't have a direct representation for SQL TIME(n), it maps it to INT64 with logical annotations, and that causes schema mismatches when the destination expects a TIME type in SQL.

So even if the column is technically TIME(2) at source and destination, the Parquet layer in the middle breaks the compatibility.

 

You can check few possible alternatives which can help you with your case:
* Intermediate Landing Table with VARCHAR (One-Time Setup). Instead of casting at source every time have your pipeline land the data into a staging table in Fabric where the time column is just VARCHAR(12). Then run a post-copy SQL statement (as a second activity) to insert into your final table where the value is cast back to TIME(2).

This avoids touching your source and logic sits entirely in Fabric and this only needs to be configured once.

* If your project allows using Dataflow Gen2 instead of Pipelines for this table. It loads data via native Power Query transformations. You can handle time column formatting more gracefully and avoid low-level Parquet serialization issues.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.