CozyRoc REST destination errors uploading due time constrains and data validation constrains
Hi, I'm facing some errors for date properties (even when the dates are set as date (not datetime) and Hubspot portal is set to EST (trail portal is also set at EST and works ok).
I am receiving this erros while uploading data:
"business_start_date"}},{"index":1,"propertyValidationResult":{"isValid":false,"message":"940824000000 is at 4:0:0.0 UTC, not midnight!","error":"INVALID_DATE","name":"date_of_first_funding"}},{"index":1,"propertyValidationResult"
2) Errors with custom properties:
(100) {"status":"error","message":"Errors found processing batch update","correlationId":"3f301f5d-c8b7-4bc3-9d01-b5908a6125c8","failureMessages":[{"index":5,"propertyValidationResult":{"isValid":false,"message":"MCA was not one of the allowed options: [label: \"Y\"\nvalue: \"Y\"\ndisplay_order: -1\nhidden: false\nread_only: false\n, label: \"N\"\nvalue: \"N\"\ndisplay_order: 1\nhidden: false\nread_only: false\n]","error":"INVALID_OPTION","name":"collections_cases"}}],"requestId":"d3a7291bf8238d8dc9b1dfa3cec847e7"} at CozyRoc.SqlServer.SSIS.RestDestination.#=zDhgJv3h37HmT(ArrayList #=zN9oB5_I$BJP$, IStatus[] #=zIxQS8Fg=) at CozyRoc.SqlServer.SSIS.RestDestination.#=zGoSSrGpEnkN5(ArrayList #=zN9oB5_I$BJP$, ArrayList #=zueeK9pltEseB) at CozyRoc.SqlServer.SSIS.RestDestination.#=zfE9XmIUxhKFx() --- End of inner exception stack trace ---
1. When looking to pass a date property value, the value must be formatted in midnight UTC. You can learn more here: How should timestamps be formatted for HubSpot's APIs?. An example: May 1 2015 would be 1430438400000 (01 May 2015 00:00:00 UTC).
2. It looks like you're attempting to pass value 'MCA' into your property - Collections Cases. I took a look into the property - Collections Cases and note that currently there's only two dropdown options - 'Y' and 'N'. If you'd like to pass 'MCA' as a value to Collections Cases property, you would need to add 'MCA' as a dropdown option value.
Hope this helps to shed some light and do let me know if you'd like further clarification on this!
CozyRoc REST destination errors uploading due time constrains and data validation constrains
Hey Wendy thanks for the reply.
While researching I also found the link you mentioned about the date time UTC; and also another posts about the Site settings for timezones.
Let me give you additional details:
I have four (4) attributes set as date (not datetime) in our source database (MS SQL 2012), using Cozyroc solution for SSIS integration, I set REST destination for Hubspot.
Here is the issue: 2 of the 4 attributes returns:
propertyValidationResult":{"isValid":false,"message":"1016773200000 is at 5:0:0.0 UTC, not midnight!","error":"INVALID_DATE"
And the other two returns: {"isValid":false,"message":"865137600000 is at 4:0:0.0 UTC, not midnight!","error":"INVALID_DATE".
Additional research pointed me to DST considerations while converting dates to UTC
The plain assumption is that I have to transform every single record (date property field) to be converted at UTC midnight considering the proper DST for the convertion. Instead of managing sourcing date field. In spite of the above, I'm still wondering why this worked ok in the trial environment. I meant: no issues with date fields.
CozyRoc REST destination errors uploading due time constrains and data validation constrains
Issue is narrowed down to custom properties/fields using date picker as data type.
I have been sending different date/time data-conbinations; however I'm receiving the same error:
"error":"INVALID_DATE","name":"first_founding_date"}},{"index":2,"propertyValidationResult":{"isValid":false,"message":"1125892800000 is at 4:0:0.0 UTC, not midnight!" (Note: regardless of the date-time sent)
Hubspot Propietary date fields like Date of birth do not return the error.
I'm still some guidance before using date picker properties instead and replace them with single line test.
The error you're seeing is because the timestamp is not set to UTC midnight. You would need to set that to UTC midnight before sending it over to property first_founding_date.