Salesforce SSIS connector & DT_DBTIMESTAMPOFFSET

[tad.d...@gmail.com]

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

[Ivan Peev]

Hi Tad,

By default the date/time columns extracted from Salesforce are converted to the server's local time. You can use Time Zone Transformation reusable script here to convert the date/time columns to the timezone you prefer.

[todd.s...@docusign.com]

Does the flip side happen with the Cozyroc connector as well?  If I pass 2017-07-01T08:00:00 < SystemModstamp as a filter to Salesforce does the date get converted from local server time to UTC?  If the date is converted can I pass the offset to keep from doing the conversion?  e.g. 2017-07-01T00:00:000-00:00 < SystemModstamp

Thanks,

Todd

[Ivan Peev]

Hi Todd,

We don't do any manipulation on the specified ObjectFilter parameter. What you specify is what will be sent to the Salesforce service for processing.