We have an issue when we copy data from oracle to ADLS using ADF(Azure Data Factory). The oracle DB has tables with timestamp values at European timezone. We use azure data factory to copy the data in to ADLS. The Data Factory IR (Integration Runtime) is on an on-prem VM that is in US Eastern time zone.
The issue is - When we copy oracle table that has timestamp (but no timezone), the ADF copy activity automatically converts the timestamp value to US Eastern Timezone. But we don’t want this to happen, we want to ingest the data as it is in the source table.
Example: Data in Oracle Table - 2020-03-04T00:00:00 ( this is in CET ) Data in ADLS - 2020-03-03T19:00:00.000+0000 ( above date got converted to US EST, since there is no timezon info in Oracle table, and its being interpreted as UTC by Spark (+0000))
Expected in ADLS - 2020-03-04T00:00:00.000+0000 (don't want timezone conversion)
Is there a way to enforce a timezone at oracle connection level in Azure Data Factory ?
We tried to set property in Oracle Linked service - connection parameters ( PFA) but this had no effect on the timezone, we still got it converted to EST. TIME_ZONE='Europe\Madrid' TIME_ZONE='CET'
Timestamp is internally converted to Datetime in ADF
Thus, In Mapping tab of copy activity, Change the datatype of source column and copy the data. Below is the approach to change type.
Click the JSON representation of the pipeline.
Edit the datatype in Json for column with timestamp to String (both in Source and sink).
Source:
Sink: