Dataverse Synapse Link to Lake Database pulling Date and Datetime fields.

Question

Dataverse Synapse Link to Lake Database pulling Date and Datetime fields.

Dax Kent 25

Our Dynamics Dataverse is using the Synapse Data Link to move data to Azure Data Lake gen 2. We then use a Synapse Analytics Data flows to transform the data to a Lake Database parquet file. Up until last week this was working great, but recently any field that is Date or Datetime related is showing up as Null in the lake database.

The Dataflow data previews in each action step show the date data being populated, and the Sink action shows this as well but on final Sink, the values are null.

I've tried dropping and recreating the Date and Datetime fields in the Lake Database tables,

dropping and recreating the tables themselves,

rebuilding the dataflows from scratch, so for none of these work.

The only solution has been to build a 2nd Date field only Table beside the initial table, and then in the data flow have 2 Sink actions, one to the primary table and one to the 2nd date field only table. This works and both tables get date fields populated. If I backtrack and try to only sink to one table, either or, the date fields return to null in the Lake Database after the sink.

Accepted answer

1 additional answer

Your answer

Answer 1

Amira Bedhiafi 32,676 Volunteer Moderator

Thank you for your update, as you cannot accept your own answer, I am reformulating it :

You created recreated the table using T-SQL, but the behavior remains unchanged.

The data is transferred only when a second Sink action runs in parallel to a second table.

If either Sink is removed, the date fields become null. However, as long as both Sink actions execute in parallel, the date values are correctly populated in both target tables.

Answer 2

Amira Bedhiafi 32,676 Volunteer Moderator

Hello Dax !

Thank you for posting on Microsoft Learn.

This sounds like a regression or change in behavior in Azure Synapse Data Flows or the Lake Database connector (or potentially the underlying Parquet writer) that affects Date and Datetime field serialization.

You can start by disabling auto-mapping in the Sink transformation and manually map each Date/Datetime column to make sure that you have the proper type alignment.

Before the Sink, you can use a Derived Column step to explicitly cast these fields using toTimestamp() to enforce correct formatting.

Then, verify that the target Lake Database table has matching data types (date or datetime) by inspecting or recreating the schema manually using T-SQL if needed. At this will prevent mismatches that lead to null values.

Dax Kent 25 Reputation points

2025-06-02T22:19:16.16+00:00

Thank you for the suggestions Amira, but unfortunately no luck.
We don't use Auto-mapping at all, I've always made it a point of manual mapping to ensure field alignment.
I tried the derived column but received an error as the toTimestamp() function doesn't like being passed an existing timestamp value. I then tried converting it first to a string and then back to timestamp both with 2 inline Derived column actions and then using a single Derived Column ustin the toTimestamp(toString()) method, but no change with either attempt.
I tried scripting and recreating the table using T-SQL with no change in behavior.

The data will move, but it requires that 2nd Sink action in parallel to a 2nd table. If one of the Sink actions are removed, the date fields are null, but as long as both Sink actions are in parallel, both tables being sinked receive date values.

My solution is clunky but it's been a solid week of troubleshooting and attempting solutions, so this fix will have to stay until whatever regression is resolved.
Thanks.

Share via

Dataverse Synapse Link to Lake Database pulling Date and Datetime fields.

1 additional answer

Your answer