question

bhushangawale avatar image
0 Votes"
bhushangawale asked HimanshuSinha-MSFT commented

Self hosted integration runtime and incorrect datetime conversion

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.

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @bhushangawale ,
Thanks for the ask and using the Microsoft Q&A platform .
I have to read the post multiple times and now I think that the I understand the ask . You have a copy activity which is having source as FTP and sink as ASQL and you are using stored procedure to insert relevant data in the SQL table .
You mentioned that its was working fine before March / 2021 . I was going through the March release and I see that in March only ADF released the FTP feature , read here . Can you please share the ADF json ? Also can you please check if you can run a lookup activity against the FTP server and see what is the modified time it returns .
Copy activities my itself just loads the data from source to sink , so I am having hard time digestiing that something is wrong there ( may be bug yet to be disovered ) .

Please do let me know how it goes .
Thanks
Himanshu


0 Votes 0 ·

1 Answer

bhushangawale avatar image
0 Votes"
bhushangawale answered HimanshuSinha-MSFT commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @bhushangawale ,
It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries .
Thanks
Himanshu

0 Votes 0 ·