Data Pipeline COPY activity datetime mapping to timestamp

[amaaiia]

I have a COPY activity in my Pipeline that ingest data from SQL Server. I don't know why, but my datetime type filed is being converted to timestamp subtracting an offset with no reason.

1. Source connection and preview data:

2. Destination table in Lakehouse:

3. Mapping for fields:

4. Run pipeline and check lakehouse final table:

As you can see, comparing step 1 preview data and step 4 lakehouse data, final table StartTimeLocal field has subtracted an offset that is not stored in source filed. Source field is of type datetime, is not datetimeoffset. Why is Fabric applying the offset?

[Anonymous]

Hello @amaaiia ,

Thanks for the using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[Anonymous]

Hi @amaaiia ,

Can you please expand the Type conversion settings in Mapping?

default settings are:

[amaaiia]

I've already tried this. But I think this conversion types is for when you have incoming string fields and have to be converted to datetime or datetmeoffset. In my case, my fiels are all datetime or datetimeoffset in source, so I'm not sure if this is usefull for me.

My main problem is how tell COPY activity not to parse datetime or datetimeoffset to UTC by default,

[Anonymous]

Hi @amaaiia ,

Copy to lakehouse table timestamp data type will by default convert data/datetime/datetimeoffset to utc based timestamp.
What particular issue are you facing? I can help more if there is an activity id.
Please share the pipeline activity id in private chat.

Thank you

[amaaiia]

The issue is that I've been using DFg2 to ingest data, and for DFg2, 2024-04-14 22:30:32, 2024-04-14 22:30:32+2:00, 2024-04-14 22:30:32+0:00 is the same, it parses these datetime and datetimeoffset values always as 2024-04-14 22:30:32 in datetime type. And all my ETLs, notebooks, etc, are prepared to work with that format,

Now, that I'm trying to use COPY activity, 2024-04-14 22:30:32+2:00 becomes 2024-04-14 20:30:32, 2024-04-14 22:30:32 becomes also 2024-04-14 20:30:32 (and here I don't know why, because source field is datetime type, not datetimeoffset, so the offset is being invented by COPY). So, the issue is that DFg2 and COPY behaviour is not the same when parsin datetime and datetimeoffset fields, so the following code doesn't work.

I'm sending pipeline ID in private chat.

[Anonymous]

Hi @amaaiia ,

At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[Anonymous]

Hi @amaaiia ,

We have a response from internal team -

Discussed with internal team, copying datetime data type to lakehouse timestamp will by design go through a UTC conversion, and the offset is decided by the region of the compute resource.

---

Incase if you have further queries.

Please reach out to our support team so they can do a more thorough investigation: Link 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Hope this helps. Please let us know if you have any other queries.

[Anonymous]

Hi @amaaiia ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If you are planning to create a support, please provide the ticket number as it would help us to track for more information.

Thanks

[amaaiia]

I've opened a support ticket: 2404100050002948

[Anonymous]

Hi @amaaiia ,

Thanks for sharing support ticket. Hope you would be able to get some solution from the team. Please share in Fabric Community if you are able to get to a resolution.