I'm having problems with date/time when trying to read data from Salesforce and push it to SQL Server.
The SystemModstamp field in Salesforce is stored in UTC time. It has time zone offset information. My target in SQL Server has a data type of 'datetimeoffset' and the goal would be to have the date come in unchanged.
By default, the data type on this field in the Cozyroc is "database timestamp (DT_DBTIMESTAMP)". The result of this is that the time gets shifted by 5 hours (I'm on EST) because the fact that it's UTC gets lost.
If I switch the data type of the output column on the Salesforce connector to "database timestamp with timezone (DT_DBTIMESTAMPOFFSET)", I get exceptions that it cannot process the data. A sample error is:
Error: 0xC002F304 at Get inserts & updates, Salesforce Source [689]: An error occurred with the following error message: "Failed to process '2016-12-14T05:57:13.000Z' for column 'SystemModstamp'.".
I'd really prefer not to have to shift the time zone of every column after I insert it in the database.
Is there some workaround for this?
Thanks,
--Tad