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.
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?