3

One way to solve the datamigration challenge in Salesforce seems to be the usage of ETL-Tools. Looking around for a free and powerful approach, I found here at SFSE some recommendations of TalenD - It is free, extremely powerful, but as a tradeoff comes along with a strong learning curve.

So I think it could be very useful for many of us to share our experiences. If you touch dataloader sometimes, just have a look at ETL tools. After a few days, I can only say it's like leaving the stone age.

This said, my current goal is to copy Opportunities with LineItems from Salesforce to Salesforce using TalenD as suggested by @codeulike here Copy Production data to Developer Sandbox following concepts described here: Copying data to Salesforce Sandboxes using TalenD

The copy-process should be repeatable using Upserts.

The datatransfer from org to org works fine for Account, Contacts and Opportunities unsig Upsert. But at OpportunityLineItem Insert and Update has to be treated differently. Reason: if you Insert an OLI, the PricebookEntryId is mandatory, however if you Update, the PricebookEntryId must be omitted, because it can't be updated after creation.

What I have now runs exactly once: the first time, as no OLIs exits on the target and all upserts will be defacto inserts. If I do it again, the error-message is: Exception in component tSalesforceOutput_7: Unable to create/update fields: PricebookEntryId. Please check the security settings of this field and verify that it is read/write for your profile or permission set.

enter image description here

Has anyone yet enough experience with TalenD just to provide a rough conceptual approach on how to repeatable Upsert Opps with LineItems? Im sure I only need to be pointed in the right direction and then I'll figure out the rest on my own.

The easy approach to simply delete all OLIs at the target works fine of course - but is not what I'm looking for.

A gut-feeling tells me, the Upsert can be optimized by tMap having more than one input connections. Having input connections form source and target, I could imagine a kind of join (or anti-join) to distinct between existing an new OLIs at the target. But how to do this in TalenD? Is there a simpler approach?

  • Looking now at talendbyexample.com/talend-tmap-component-joins.html there joins and anti-joins are explained a bit with examples. As anticipated in the question, tMap seem to be the key to several kingdoms... Trying to adjust it to my use case now. Any other ideas still welcome! – Uwe Heim Aug 16 '14 at 11:21
3

After playing around with tMap a little bit, I got it to work somehow.

A prerequisite is as described by @codeulike to have externals field on the destination-org for all relevant objects to hold the original Salesforce-Ids of the source-org. Therefore I added a ProductionId__c to Opportunity, OpportunityLineItem, Contact, Account and all other entities I'm copying (text(18), external, unique).

As I expected tMap was my best friend. This idea is the following:

  • create 2 tSalesforceInput components
    • the first is to read all OLIs of the Source-Org (I called it SrcOLIs)
    • the second to read all OLIs form Destination-Org (I called it DstOLIs)
  • create a tMap and connect both tSalesforceInputs as inputs
  • open tMap and join SrcOLIs.id on DstOLIs.ProductionId__c (this told me how)
  • now comes the magic: in tMap at the output field "PricebookEntryId" I used the expression Relational.ISNULL(DstOLIs.Id) ? SrcOLIs.PricebookEntryId : null which pulls me all existing OLIs to NULL
  • finally I needed to activate at the tSalesforceOutput on "Advanced Settings" the checkbox "Ignore NULL Fields"

Since TalenD seems to be more a kind of point-and-click-tool, I provide you some screenshots to illustrate the approach:

Subjob Configuration and Overview Subjob Configuration

tMap Configuration, Join and Expression Setup tMap Configuration

Note that the second output is just optional. I used a tFileOutputExcel to verify what is going on. But this XLS output was extremely helpful for me during the debugging.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.