Self hosted integration runtime and incorrect datetime conversion

Question

We have started facing this issue around late in March 2021 and still continues.

We have a setup where we read last modified datetime of a CSV file stored on a FTP via self hosted integration runtime and send it to a copy activity that copies contents of this CSV to the SQL Azure database table by calling a stored procedure that also inserts this last modified datetime of the csv along with contents of csv file to same table in rows.

It's relatively simple and quite straight forward setup and had been working for almost over 2 years, however from March 2021 the copy activity has started putting the date incorrectly. It always adds +10 hours to the input datetime i.e. last modified date of the csv feed to the copy activity.

Note that the we also tried replicating this entire setup in another Azure environment where all services like SHIR, SQL Azure database and file servers are hosted in Central India region while ADF is in Southeast Asia region and still can see the same behavior.

Stored procedure is having only insert statement to a table and does not do any explicit date time conversion. When Copy activity calls it (while making use of self hosted integration runtime to read from source), it always adds +10 hours to input datetime and then inserts it to the SQL Azure database table.

When this stored procedure is called directly (not from copy activity that is making use of SHIR to connect to source) then it adds correct value to the datetime column so clearly something is not right with SHIR's functional behavior.

Any input on this would be appreciated.

Answer 1

Thanks for your response, after multiple attempts I think I could get to the root of it, however I am still experimenting with this so will be in a better position to confirm after detailed testing.

It's basically the time zone of the machine hosting SHIR. When you change the time zone of the machine and restart it, it ensures to pick up the updated time zone and any operation e.g. insert statements in stored procedures executed via linked service that makes use of this SHIR will insert the data e.g. datetime in storage table (SQL Azure Database in our case) w.r.t. to the time zone of the machine and that is fine.

It's just that the SHIR service or machine needs to be restarted in order to correctly refer to the updated time zone, I was mostly missing this bit and hence caused the confusion.

Thanks for reading through it and responding.