Data Pipeline truncate datetimeoffset with COPY activity

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.

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.

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

amaaiia  v-cboorla-msft 

I don't know if you noticed, but the Copy data activity running in data gateway is not converting source timestamps to the correct UTC, meaning it does not take into account whether the incoming timestamps occurred at the time when DST was on or not.

In other words,  at the moment DST is off, and so UTC is +5 hours from EST.  But if a timestamp from Winter 2023 comes in, when DST was on, the UTC conversion in the data gateway erroneously adds +5 to the timestamp instead of +4.  DST is on in the Winter and we lose 1 hour, so the time difference between EST and UTC is +4, not +5.

At source, SQL Server does the conversion properly, so no issues there. But all my years of data have wrong dates and times, and I suspect every other user of this pipeline activity also does if they do not work in UTC by default.