It seems that Time Zone Index Value in `convertFromUtc` Function does not match in `Preview data` and the actual Pipeline run.

Question

I want to copy a delimited text in Azure BLOB Storage ( Source ) to a table in Azure Database for MySQL ( Sink ) using Azure Data Factory's Copy Activity.

At the Additional colums of Source tab, I set a dynamic content ( @formatDateTime(convertFromUtc(utcNow(), 'Tokyo Standard Time'), 'yyyy-MM-dd HH:mm:ss.fffffff') ).

When I clicked Preview data, the following error occurred.

• Error details
  • Error code: FailToResolveParametersInExploratoryController
  • Details
  • The parameters and expression cannot be resolved for schema operations. Error Message: {
    "message": "ErrorCode=InvalidTemplate, ErrorMessage=In the function 'convertFromUtc', the value provided for the time zone id 'Tokyo Standard Time' was not valid. "
    } Activity ID: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

Then, I edited Tokyo Standard Time to Asia/Tokyo, data preview worked.
However, when I run the pipeline with Debug, the following error occurred.

• Error details
  • Error code: InvalidTemplate
  • Failure type: User configuration issue
  • Details
  • In the function 'convertFromUtc', the value provided for the time zone id 'Asia/Tokyo' was not valid. '.

Then, I edited Asia/Tokyo to Tokyo Standard Time, the pipeline worked.

It seems that Time Zone Index Value in convertFromUtc Function does not match in Preview data and the actual Pipeline run.

I think that Time Zone Index Value should be matched in preview and actual pipeline rune.

Do you think about that? Is the current behavior right or valid?

Answer 1

Hello @Makoto Oda ,

Thanks for the question and using MS Q&A platform.

My understanding is that when you are using Tokyo Standard Time in convertFromUtc function in additional columns of your source, while doing data previw you are seeing errors mentioned above. So, to work around, you have changed Tokyo Standard Time to Asia/Tokyo and the data preview worked fine. And when you debug run the pipeline it failed with error mentioned above. So then you have again changed it back to Tokyo Standard Time from Asia/Tokyo and then the debug run was successful. So, you would like to understand what the correct behavior is. Please correct if my understanding is incorrect.

In order to validate the feedback, I tried to reproduce the issue on my end using the same dynamic expression you have provided above - @formatDateTime(convertFromUtc(utcNow(), 'Tokyo Standard Time'), 'yyyy-MM-dd HH:mm:ss.fffffff') and tried to preview the data, unlike your scenario the preview data was successful without any issues. I also did data preview in mapping
section and the preview were successful here too. Please see below GIF for additional justification.

Hence, my pipeline source data preview was successful for Time Zone Tokyo Standard Time.

I also tried using Asia/Toky in dynamic expression, but the data preview is failing with error the value provided for the time zone id 'Asia/Tokyo' was not valid

My understanding from these tests is that Tokyo Standard Time is correct one to use and it should work as expected when you do preview data or debug run pipeline. But if you are able to reproduce the issue consistently on your end, then I would recommend filing a support ticket for deeper analysis as we weren't able to reproduce the issue on our end.

Additional info:
While working on the issue, I noticed that you are using formatDateTime() function to convert the format. You can avoid using that additional function as you can specify the format type in convertFromUtc() itself. For more info, please refer to this documentation - ADF control flow expression language functions#convertFromUtc

Hope this info helps. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators