Copy Activity in Data Pipeline TimeZone defaulting to UTC

Question

i  did use the Copy Activity in a Data Pipeline to copy data from an on premise oracle db that has a timezone ("America/NewYork") to a Fabric lakehouse. In the source tab of the Copy Activity i had chosen a query as the Use Query option, where i am running select SQL statements with some cast of number data columns and creating some concatenated tables. What i am seeing after the copy is completed, is that my date columns are having a UTC time zone . How can i fix that?

Note: I have already updated the OnPremise Gateway to the latest version 3000.242.8 following the post: here however this didn't solve my problem

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

To ensure the timezone is preserved, here are some steps and checks you can perform in the Copy Activity configuration:

1. Modify the Source Query: Explicitly convert your date/time fields to the desired timezone in your SQL query. For Oracle, you can use FROM_TZ and AT TIME ZONE to cast datetime fields to "America/New_York":
2. Mapping with Conversion in Data Flow (If Applicable): If using a Data Flow, explicitly apply a timezone transformation in the Data Flow itself to ensure consistency.

Set Pipeline Parameters for Timezone Awareness: Add parameters to the pipeline to specify source and destination time zones. You can then use these parameters to adjust date formats or offsets before loading.

Check Fabric Lakehouse Ingestion Settings: In the destination settings, check if there is an option for timezone handling. Fabric lakehouses often default to UTC but may have configuration options for timezone retention or conversion during ingestion.

Validation Post-Copy: After applying changes, validate by running a subset of data to ensure the timestamps align with the "America/New_York" timezone in the lakehouse.

Let me know if this resolves your timezone issue

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.