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

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

Answer 2

@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.