Dynamic formatting of last modified filter in Data factory DataSet

Question

I'm trying to set the last modified filter in a azure data factory dataset dynamically.

I'm using the following expression:

@formatDateTime(adddays(utcnow(),-2),'yyyy-mm-ddThh:mm:ss.fffZ')

I'm getting the following error:

Activity Copy1 failed: Failure happened on 'Source' side. ErrorCode=UserErrorInvalidValueInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to convert the value in 'modifiedDatetimeStart' property to 'System.Nullable`1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' type. Please make sure the payload structure and value are correct.,Source=Microsoft.DataTransfer.DataContracts,''Type=System.Reflection.TargetInvocationException,Message=Exception has been thrown by the target of an invocation.,Source=mscorlib,''Type=System.FormatException,Message=The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.,Source=mscorlib,'

I'm also not able to preview the data with this filter. I guess something is wrong here. Any ideas?

Answer 1

From the error message I understand that the string represenation of the date is not supported by the calander.

The DateTime represented by the string is not supported in calendar 

Why do you need to format the string for the comparison?

Answer 2

Actually the following commands are tested & working after publish & trigger:

@utcnow()

@adddays(utcnow(),-2)

It's the preview functionality in the front end that is not able to deal with the expressions. This will hopefully be solved by Microsoft.

Answer 3

Perhaps, as a workaround, you could use this expression to get a rid of the extra characters in your datetime expression:

@substring(formatDateTime(adddays(utcnow(),-2), 'o'), 0, 23)

I tested this with utcnow() and it should return the datetime in the desired format:

"value": "2019-04-12T10:11:51.108Z"

Answer 4

turns out you can solve the above prepending a conversion to string to your line above, so from

@formatDateTime(adddays(utcnow(),-2),'yyyy-mm-ddThh:mm:ss.fffZ')

change it to

@string(formatDateTime(adddays(utcnow(),-2),'yyyy-mm-ddThh:mm:ss.fffZ'))

it works on my end

Answer 5

Encountered the same problem in data flow:

currentUTC()

did not work for pulling the last modified file in blob storage but

currentTimestamp()

did