Data Pipeline truncate datetimeoffset with COPY activity

[amaaiia]

I'm ingesting some data from a SQL Server into my Lakehouse with COPY activity. My data in source has one StartTimeLocal column (type datetimeoffset) as follows:

When data is ingested into Lakehouse, StartTimeLocal column is parsed into Datetime2 column, but with offset time subtracted. That is:

I want the date in Lakehouse to be datetime2 type but with the date is appearing in source:

2024-04-08 03:30:35.123333

2024-01-09 01:00:09.210000

How can I achieve that?

[amaaiia · Accepted solution]

@Element115 the answer they gave me yesterday was: 

"I got a confirmation from my team that this is an expected behavior and documentation updating is in progress. As of now there is no ETA for this release."

So, there's not a solution for now. In my case, as I have access to the source, I have created an additional column in source that is my date column transformed into UTC+0:00, so that, when I ingest the date in datetime format it keeps as in source.

Is not the best solution but is the only way I've found.

[Element115 · Accepted solution]

@amaaiia  @v-cboorla-msft 

I found a workaround.  Bear with me as it is going to be a little tricky to explain in writing.  

My Refresh_Date is a SQL Server DATETIME2 type and the timestamps it stores are all in EST.  So to prevent the dreaded conversion to UTC, I now cast this colum to type STRING with the 'Copy data-->Mapping-->New mapping' setting.  You must specify all the columns that you are loading from your source view, because if you do not, that is, if you only specify the column that you want to convert to STRING, then no other column will be loaded into the LH. 

BUT... the 'Table action' under Destination setting needs to be set to 'Overwrite.'

Now, before anyone screams 'This is asinine! This is BS! We are not going to ingest everything at every refresh from scratch!', fret not.  Here's how to do it.

It just so happens, that I implement an incremental refresh in my pipeline (PL) and this architecture is perfect for this.  With a conditional activity, you check whether this is the first time the PL is trying to load the data.  If it is, then you use a 'Copy data' activity set like above, ie in Overwrite mode.  This determines the schema of your table in the LH. 

The second and more times the PL runs, the execution path needs to take the other branch of the conditional, namely the table already exists.  In that case, only load the new data since last load. And this time, the 'Copy data' activity settings are different and as follows:

Table action = Append instead of Overwrite  

The Mapping settings are now empty because the first run also created the table schema, so now any subsequent run will automatically cast your DATETIME2 timestamp to type STRING without need to explicitly specify the mapping again.

And finally, when creating your report with PBI Desktop, when connecting to this table from Power Query Editor, just change the column type from TEXT to DATETIME, and you have now your timestamps in their original timezone. Or simply offset the timestamps at the source in a view so that when it goes through the data gateway, the timezone shift  applied by Copy data brings it back to the source timezone. 

I also have opened a support ticket for this and will be talking with a support engineer tomorrow.  If there is any progress on that front, I'll let you know here.

[Element115 · Accepted solution]

It's official now. I got a response directly from Microsoft that the product team acknowledges this is a bug, and it is implied that it is the on-prem gateway that is the culprit:  

Re: QUESTION::PIPELINE::COPY DATA::MAPPING::DATETI... - Microsoft Fabric Community

[v-cboorla-msft]

Hi @amaaiia 

Thanks for using Microsoft Fabric Community.

Apologies for the delay in reponse.

Following up to see whether your query got resolved or still facing the issue. If the issue still persists, please do let us know. Glad to help.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help,

Thanks.

[amaaiia]

Still same issue

[v-cboorla-msft]

Hi @amaaiia 

Apologies for the inconvenience caused and delay in the response from my end.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support

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

Thank you.

[amaaiia]

Ticket opened: 2404100050002948

[Element115]

@amaaiia Did you get a resolution on this?  I am having the exact same issue, namely all my incoming datetime2, which is in EST, is auto converted by Copy data to UTC time.  I have tried to specify a formatting string in the Mapping settings of Copy data but to no avail.  

Don't know what more to do at this point except to take out the rope again.  😉

[amaaiia · Accepted solution]

@Element115 the answer they gave me yesterday was: 

"I got a confirmation from my team that this is an expected behavior and documentation updating is in progress. As of now there is no ETA for this release."

So, there's not a solution for now. In my case, as I have access to the source, I have created an additional column in source that is my date column transformed into UTC+0:00, so that, when I ingest the date in datetime format it keeps as in source.

Is not the best solution but is the only way I've found.

[Element115 · Accepted solution]

@amaaiia  @v-cboorla-msft 

I found a workaround.  Bear with me as it is going to be a little tricky to explain in writing.  

My Refresh_Date is a SQL Server DATETIME2 type and the timestamps it stores are all in EST.  So to prevent the dreaded conversion to UTC, I now cast this colum to type STRING with the 'Copy data-->Mapping-->New mapping' setting.  You must specify all the columns that you are loading from your source view, because if you do not, that is, if you only specify the column that you want to convert to STRING, then no other column will be loaded into the LH. 

BUT... the 'Table action' under Destination setting needs to be set to 'Overwrite.'

Now, before anyone screams 'This is asinine! This is BS! We are not going to ingest everything at every refresh from scratch!', fret not.  Here's how to do it.

It just so happens, that I implement an incremental refresh in my pipeline (PL) and this architecture is perfect for this.  With a conditional activity, you check whether this is the first time the PL is trying to load the data.  If it is, then you use a 'Copy data' activity set like above, ie in Overwrite mode.  This determines the schema of your table in the LH. 

The second and more times the PL runs, the execution path needs to take the other branch of the conditional, namely the table already exists.  In that case, only load the new data since last load. And this time, the 'Copy data' activity settings are different and as follows:

Table action = Append instead of Overwrite  

The Mapping settings are now empty because the first run also created the table schema, so now any subsequent run will automatically cast your DATETIME2 timestamp to type STRING without need to explicitly specify the mapping again.

And finally, when creating your report with PBI Desktop, when connecting to this table from Power Query Editor, just change the column type from TEXT to DATETIME, and you have now your timestamps in their original timezone. Or simply offset the timestamps at the source in a view so that when it goes through the data gateway, the timezone shift  applied by Copy data brings it back to the source timezone. 

I also have opened a support ticket for this and will be talking with a support engineer tomorrow.  If there is any progress on that front, I'll let you know here.

[v-cboorla-msft]

Hi @amaaiia 

Glad that you were able to find some insights and thank you for sharing the same with the community as it can be helpful to others.
Please continue using Fabric Community for further queries.

Thank you.

[v-cboorla-msft]

Hi @amaaiia 

Thank you for sharing the details.