Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Salesforce Update/Upsert

  1. #1
    Join Date
    May 2012
    Posts
    3

    Default Salesforce Update/Upsert

    Hello,
    I am trying to update a salesforce custom object with values from a local database. I'm trying to stream the output of a sql query to the Salesforce Update or Upsert module, and I'm not having luck getting either one to update via the API. Has anyone successfully configured those modules? I haven't been able to find any documentation on them so I'm guessing it's a configuration issue. I want to do my update based on a field from the sql results matching a value in a column in the custom object, but I'm not sure how to specify that. I tried setting Use External id? to Y in the update module, but I'm getting errors in that as well. Any guidance would be appreciated!
    Thanks,
    Sarah

  2. #2
    Join Date
    Aug 2012
    Posts
    4

    Default

    I'm running into a similar issue with the Upsert and wish there was better documentation. Are/Were you getting this error? I deleted the timestamp and the name of the transformation, but otherwise this is a copy/paste from the Logging tab in Spoon.

    Failed in writeToSalesForce: org.pentaho.di.core.exception.KettleException:

    ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unable to find object type in external key syntax!
    ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Syntax for external key is : object:externalId/lookupField

    I am using an external Id, but am not sure how to fix this syntax issue. I read through this JIRA entry: http://jira.pentaho.com/browse/PDI-3813 and it's not clear whether the issue I am having is the same as the error being described.

    BTW, when I generate a CSV file of the fields I want to Upsert and run the Salesforce Data Loader manually, it works.

  3. #3
    Join Date
    May 2012
    Posts
    3

    Default

    I did finally get this working after a massive amount of frustration. Apparently the upsert/update modules are finicky about the order of the incoming data stream . It looks as if you can just adjust the field order in the fields grid but that doesn't do anything. You have to have the I'd field first and the following fields in the same order of the update.

    What I did was write my input transformation data out to a text file then compare it to a CSV that I was able to load. That was how I figured out the ordering issue. Hope it works for you but if you run into issues let me know. Also with noting that the modules do not use the bulk API so you may have to crank up your timeout if you're working with a large dataset.

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Perhaps you can reorder your fields with a Select Values step right before the Update/Upsert step?
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Aug 2012
    Posts
    4

    Default

    Quote Originally Posted by ssievel View Post
    I did finally get this working after a massive amount of frustration. Apparently the upsert/update modules are finicky about the order of the incoming data stream . It looks as if you can just adjust the field order in the fields grid but that doesn't do anything. You have to have the I'd field first and the following fields in the same order of the update.

    What I did was write my input transformation data out to a text file then compare it to a CSV that I was able to load. That was how I figured out the ordering issue. Hope it works for you but if you run into issues let me know. Also with noting that the modules do not use the bulk API so you may have to crank up your timeout if you're working with a large dataset.
    Wow. This is a big help. I was going to go to my data loader file and see how it worked. This gives me a huge hint.

    Thanks!
    Rory

  6. #6
    Join Date
    Aug 2012
    Posts
    4

    Default

    Quote Originally Posted by darrell.nelson View Post
    Perhaps you can reorder your fields with a Select Values step right before the Update/Upsert step?
    Thanks again for another good hint. I'll try this out too.

    - Rory

  7. #7
    Join Date
    Aug 2012
    Posts
    4

    Default

    Okay, I managed to get it working for some of my upserts. I have now run into an issue with a field validation failing, which is a good thing since it tells me that the upsert is doing its thing. So yes, the order of the fields mattered and also in my case, I was picking the incorrect External ID at first. I had two issues to deal with.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.