Need help with Salesforce Date issue

[btellington]

I'm using Informatica Powercenter to move data between Oracle and Salesforce.com. When I send a date from Oracle to Salesforce, it's getting set to a Date/Time of four hours before the actual date value. So, when I send 5/4/2010 it's being recorded as 5/3/2010 8:00PM. That makes the date a day earlier when displayed, and the two systems don't match.

The Informatica server is set to GMT, although the user is set to EST. The Salesforce User has been set to both EST and GMT. Through a variety of tests, it's pretty obvious that this is happening because Informatica is giving Salesforce a specific Date/Time, and Salesforce is interpreting it wrong. In other words, it's a problem on the Salesforce side, but one caused by what Informatica is doing.

The problem is likely to be the date format which Informatica is sending to Salesforce.com. The Salesforce documentation says it needs to be in YYYY-MM-DDThh:mm:ssZ format (GMT time). I've overridden the date/time format in my session, but it's still subtracting four from my date.

How can I check to see that the over-ride format is what's actually being sent? And has anyone had a similar problem and solved it? We go live next week, and the client is really upset about this.

 

[blom0344]

Did you activate verbose logging on the expression part of the mapping? You can then check the logfiles what happens with the transformation..

Ties Blom

 

[btellington]

We turned on Verbose logging everywhere. We also set up a Salesforce success file, and in it the date format is what Salesforce is expecting.

We're sending 2010-05-04T00:00:00Z to Salesforce, but it's storing as 05/03/2010 8:00PM. If I add four hours in the mapping it shows up as midnight. It's consistently subtracting four hours, and I don't know where that's coming from.

The informatica server is on GMT. The user that Informatica runs under is on EST. The Salesforce organization is on EST by default. The Salesforce user that's used to insert the data is on EST.

When we change the field type on Salesforce from a Date/Time to a Date, it goes in as 5/4/2010.

I really don't know what's going on here.

 

[blom0344]

As a reference: Write INFA's output to a flatfile and import the data into Salesforce by means of a script.
Does that change things?

Personally I would not use a direct write from ETL to an application database. I would write to a (set of ) importing tables that are truncated each time. In a second step perform the required insert/update against the application tables from the temp set of tables

Ties Blom

 

[btellington]

Unfortunately, it's part of a regular data feed that runs every night to keep two systems in synch.