Azure Data Factory ServiceNow Connector Copy Activity Dynamic Date Filter Query Builder

Question

Hello all,

We recently received a notice to update our legacy ServiceNow connection driver and while updating our copy pipeline activities, I am struggling to add a filter on a date column. In the legacy connection driver, we were able to use a query that contained a dynamic date on the where clause. However, with the new connector, the only option is to use query builder and when trying to filter on table alm_hardware, sys_updated_on column I cannot use a dynamic date. When I use the dynamic expression below, I receive an error 'String' does not match the field, expected type is 'object'. If I manually type the date on the value box of the Query Builder, the filter works. However, I want a dynamic date for incremental load reasons.

formatDateTime(adddays(utcnow(),-2), 'yyyy-MM-dd ')		

FYI, I have also tried wrapping the code below in the JSON conversion function which appears to be a valid object type, but received the message on the image below.

Answer 1

Hi David Gonzalez,

Thanks for reaching out to Microsoft Q&A.

Observed Issues:

Dynamic Date Expression: When using the dynamic expression 'formatDateTime(adddays(utcnow(),-2), 'yyyy-MM-dd')', it results in an error saying the field is a 'String' but the expected type is 'object'.

Manual Date Works: Manually entering a static date works fine, but the goal is to use a dynamic date to support incremental loads.

Error Explanation

The error in the screenshot ('ErrorCode=InvalidTemplate, ErrorMessage=The function 'json' parameter is not valid...') indicates that ADF is not able to parse the dynamic date string correctly. It seems that the query builder expects the date in a specific format or as an object type, and the dynamic expression is being interpreted as a string.

Probable approach:

• Remove Trailing Spaces: Ensure there are no trailing spaces in your dynamic expression. You can adjust your expression to:
  • formatDateTime(adddays(utcnow(),-2), 'yyyy-MM-dd')
• Convert Date to Object Type: Since the error suggests that it expects an object, try wrapping the result in a conversion function that outputs the correct type:
  • json(formatDateTime(adddays(utcnow(),-2), 'yyyy-MM-dd'))
• Check ServiceNow API Compatibility: Ensure the API is receiving the date in a format that it supports. Some APIs may require more specific formatting or handling for date fields.

If direct conversion doesn't work, another approach could involve breaking the filter logic into multiple steps or using a Lookup activity to resolve the dynamic date first and then passing it to the filter query.

Try these adjustments in your query and check if the connector processes the dynamic date without errors. If the issue persists, please share more details, and we can dive deeper into alternative approaches or configuration fixes.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

I was able to resolve the issue by copying the dynamic expression code directly into the value box.

Answer 3

@David Gonzalez

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask:

We recently received a notice to update our legacy ServiceNow connection driver and while updating our copy pipeline activities, I am struggling to add a filter on a date column. In the legacy connection driver, we were able to use a query that contained a dynamic date on the where clause. However, with the new connector, the only option is to use query builder and when trying to filter on table alm_hardware, sys_updated_on column I cannot use a dynamic date. When I use the dynamic expression below, I receive an error 'String' does not match the field, expected type is 'object'. If I manually type the date on the value box of the Query Builder, the filter works. However, I want a dynamic date for incremental load reasons.

formatDateTime(adddays(utcnow(),-2), 'yyyy-MM-dd ')		

FYI, I have also tried wrapping the code below in the JSON conversion function which appears to be a valid object type, but received the message on the image below.

Solution: I was able to resolve the issue by copying the dynamic expression code directly into the value box.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.