Forum Discussion

amaaiia's avatar
amaaiia
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

Data Pipeline COPY activity datetime mapping to timestamp

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. Sourc...
data engineering
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

Hi amaaiia ,

Can you please expand the Type conversion settings in Mapping?

default settings are:

 

  • amaaiia's avatar
    amaaiia
    Icon for Skilled Sharer rankSkilled Sharer
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        amaaiia
        Icon for Skilled Sharer rankSkilled Sharer
        2 years ago

        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.