datetime is not appearing in copy activity

[dinesh01]

In copy activity data source is SQL server and destination is lakehouse. In source side there is a column with data type 'datetime' but in sink side only 'date' and timestamp properties are appearing. I seleceted timestamp property I am lossing 4 hours because source is hosted UAE region and SQL endpoint timezone is UTC. But this is not happening in DataflowGen2. 

[dinesh01]

I am not able to apply expressions and functions in mapping section of copy activity. The destination is lakehouse. I am getting option only Date and timestamp. I would like to apply datetime in the destination side.

[v-cboorla-msft]

Hi @dinesh01 

Apologies for the delay in response from my end.

Could you please share the screenshot of your mapping section of copy activity. This will help me to understand and guide you in a better way.

Thanks.

[v-cboorla-msft]

Hi @dinesh01 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. 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.

[v-cboorla-msft]

Hi @dinesh01 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.

[Ayybee]

It sounds like you're encountering a time zone conversion issue when copying data from a SQL Server data source to a lakehouse destination.

1. Ensure that the time zone settings are consistent across your SQL Server instance, your lakehouse destination, and any other components involved in the data pipeline.
2. Since your source SQL Server is hosted in the UAE region (which is 4 hours ahead of UTC), you may need to adjust the timestamp data during the copy operation to reflect the correct UTC time. This can be done by applying the appropriate time zone conversion logic within your data pipeline.

[v-cboorla-msft]

Hi @dinesh01 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
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.

[dinesh01]

I am not able to find the appropriate time zone conversion logic within the copy activity of data pipeline.

[v-cboorla-msft]

Hi @dinesh01 

Apologies for the inconvenience.

Please refer to the below mentioned documentations for time zone conversion logic within the copy activity of data pipeline.

Link1 : Expressions and functions for Data Factory in Microsoft Fabric

Link2 : Convert a timestamp from the source time zone to the target time zone.

For additional information please refer : how can we convert timezone in azure data factory to specified timezones.

I hope the above information helps. Please do let us know if you have any further questions.

Thanks.

[v-cboorla-msft]

Hi @dinesh01 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. 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.

Thank you.

[dinesh01 · Accepted solution]

after changing the gateway server timezone to UTC, this issue is resolved. This is applicable when gateway is used in datapipeline. Dataflowgen2 does not require the change of timezone.