Salesforce.com Upsert?

dataservices job design
1

Hello,

We’re trying to get the Salesforce.com Adapter to work in a similar fashion as the seperate data loader tool.

In the data loader, we just supply a specific business key (a customer key, company code, etc) but do not supply the Internal ID code that SFDC (=SalesForce Dot Com) uses.

SFDC automatically detects if a record is new (insert) or already exists - and then update its information (Update). So under water, the data loader tool must trigger the UPSERT() command in the Salesforce API.

However, in our test environment we tried to get the BODS SFDC Adapter to behave the same way but here are our findings:

  1. If we leave the internal ID field empty, we get an error.
  2. If we populate the Internal ID field with something, along with the other attributes in our table, it works. SFDC actually insertst the record.
  3. If we run the DF again, we get a “Duplicate key” error - which makes perfectly sense but how do we get it to USPERT / Update the record?

(and before someone asks, no the SFDC Adapter does not have the auto correct etc options like a database table object would have)

We are currently on BODS 12.2.3.2 - so our SalesForce.com Adapter only supports API 16.

However, upon checking the SFDC API documentation, UPSERT() has been around since API 7 so API 16 should definitely support it.

We know that BODS 12.2.3.3. actually contains an update of the Salesforce.com Adapter which allows us to only supply partial tables - rather than having to supply ALL fields in a table when loading data into SFDC. But I am not sure how this relates to our problem - certainly people have been using the Salesforce.com adapter succesfully BEFORE 12.2.3.3?

I’ve put this post in this section because I believe we need some help in designing our DFs to do what we need. It is not a connectivity issue as our Salesforce.com adapter is working fine - it can talk to SFDC just fine but for some reason we cannot get it to ‘behave’ the way we want to.

And while the documentation that comes with BODS is excellent - the section on the SalesForce.com Adapter is very very thin indeed and mostly focussed around installing and configuration, but not actual usage.

Anyone here that has been using the Salesforce.com adapter to insert and update records? We’d love to hear from you :slight_smile:

ErikR :new_zealand: (BOB member since 2007-01-10)

2

DS 4.0 uses version 21 as I recall. Thats from memory so I could be off.

eganjp :us: (BOB member since 2007-09-12)

3

Thanks Jim, that is indeed correct. But we should be able to get this to work with the current version of BODS we’re using?

ErikR :new_zealand: (BOB member since 2007-01-10)

4

I don’t know for sure. I’ll check. The client I’m working with is using DS 12.1 for development and then trying stuff in 4.0. There is a new 4.0 adapter feature that we are working with SAP on to get it to do some of the same things that the SFDC loader does.

eganjp :us: (BOB member since 2007-09-12)

5

Jim, any help from your end would be massively appreciated. :smiley:

People at my end are wondering if we should be writing our own webservice calls through BODS - by passing the SFDC adapter - and I am trying to prevent us from going down that path. (Something like lemmings throwing themselves willingly from those high cliffs!)

My argument being that the SFDC Adapter has been around since BODI 11.x so surely it must be work?

ErikR :new_zealand: (BOB member since 2007-01-10)

6

Hello Erik,

You can try using Map_Operation and change the opcode from Normal to Update and send it to SFDC target table.

sameer81 :new_zealand: (BOB member since 2007-09-15)

7

Just so you know, I do not work with the adapter on a daily basis. Not even weekly. My current client has a project that is actively using the SFDC adapter. I am peripherally involved with that project and I’ve helped them resolve a few issues - mostly dealing with performance. By no stretch of the imagination should I be considered an expert on anything having to do with SFDC or the DS adapter. I’ve set up my own SFDC sandbox, loaded data to it, extracted data from it, etc, etc. For what I was doing (simple stuff) the performance was pretty good.

I asked the client the question about the upsert functionality and they said it’s the new feature in 4.? (yet to be released).

With regard to bypassing the adapter I have to say that there is a powerful argument for doing so. The performance of the APEX Data Loader far surpasses what the DS SFDC adapter can achieve. If you’re dealing with a few hundred rows at a time then bypassing the adapter is probably not worth your time. The client is dealing with 25 million “contacts” so performance is important. See this thread as an example.

I’m not convinced that it is necessary to completely abandon the adapter. But it sure does seem slow.

eganjp :us: (BOB member since 2007-09-12)

8

I think you will be able to use upsert from DS 4.0 SP3, you will see an option to do auto correct load in the target SFDC

manoj_d (BOB member since 2009-01-02)

9

Thanks for that - but how can we get this to work with DS 3.2? (12.2.3.2)

Upgrading to DS 4.x is not in the cards at the moment.

ErikR :new_zealand: (BOB member since 2007-01-10)

10

No, I don’t think there is any plans to backport this to 12.2 release

manoj_d (BOB member since 2009-01-02)

11

We now have this working (somewhat) - we were given the wrong URL, which is why we were having problems inserting data.

We now pull all data from SFDC into our staging tables, then using a TC to compare our SAP BW data with SFDC and sort out the new inserts and any changes (updates), discarding those records that were unchanged. We ignore deletes for now.

By splitting the DF by TC Operation code (I/U), we then “feed” the adapter with inserts (without supplying the SDFC internal ID) and updates (with the SFDC internal ID) and that should work.

ErikR :new_zealand: (BOB member since 2007-01-10)