8

Assuming metadata is already identical, the data-transfer of complex models can still take hours or even days of manual work.

Fortunately there are free tools like TalenD which can help to automate the process as described here Copying data to Salesforce Sandboxes using TalenD by @codeulike - I worked through the example and it works fine. Still it's a huge piece of work, but at least, the outcome is repeatable and the second run goes with one click.

Like doing it manually, the challenge are the references. To build up proper references you have to create muiltiple jobs in the right order, like this:

  • first Users, then Accounts, then Contacts, then Opportunities, ...

Now I come to the point of self references or other kinds of cyclic relations... Let's take a simple example like Account.ParentId. I think the basic approach goes like this:

  • in a first subjob upsert the accounts with empty Account.ParentId
  • in a second subjob update the account with the right "Relationship management for upsert" in the "Advanced settings" at the tSalesforceOutput component

My questions are:

  1. can it be done easier? Maybe in a single subjob?
  2. is there a best practice approach to solve self-references and cycles?
  3. assume we can only migrate a subset of Accounts due to capacity limits of the target org. We have to make sure that Parents are automatically fetched. Is there an smart approach for a "Query Condition" for tSalesforceInput or any other trick to add all parents automatically to an filtered subset of all Accounts?
  4. is there a way in talend to automate this dependency-layering-process and make it somehow a bit repeatable for different orgs and schemas?
  • Self-references are generally awkward I'm afriad. I don't know of any better method than what you suggest - two passes, setting the self reference the second time. – codeulike Aug 17 '14 at 11:38
7

For objects with self-reference (aka "Hierarchy") fields, the best method is to load in two steps:

  1. Load all records but leaving out the ParentId field (remove it from Talend schema).
  2. Update all records to set ParentId values (map only Id or Ext-Id, and the ParentId).

For 2 objects with cyclical references, the approach is similar:

  1. Load all records of Object-A, but leaving out the Lookup-B field.
  2. Load all records of Object-B, including its Lookup-A field.
  3. Update all records of Object-A to set Lookup-B values.

Note: Your suggestion also works, but only for 2-level hierarchies. You assume that parent records have no ParentId, but in a 3-level hierarchy, the "middle" record is both a parent and a child, so the "where ParentId = null" is not sufficient. The first method I recommended above will work for any # of levels.

An alternative method takes only 1 loading step, but I don't like it because you can't use BulkAPI w/parallel, sorting gets hard for 3+ levels, and you are at mercy of Salesforce honoring the order. But FYI, this method is: 1) Sort the source data so parent records appear first, then the children. 2) Load all data, including the ParentId. Using standard API (not Bulk/Parallel) the data normally loads in same order it appears in your source file, so the parents get created before the children that reference them.

Your other questions belong in a separate question post. But regarding how to ensure referred-to records are included/added in partial-data migrations, I always approach these scenarios by first building a list of distinct Id's (External-Id's) that are needed, and then loading records whose Id is in that list (using Inner Joins). For example, say you want to migrate a sampling of Accounts, only those whose name begins with 'A', plus ensuring all of their parent Accounts (regardless of Name) are also included. I would create a list of all account Id's that begin with 'A', then append to that list all the ParentId's of accounts that begin with 'A' but which are not already in the list. Finally, when loading the Accounts, I would inner-join Accounts to that list. The overhead of building the list seems high for one object, but it pays off because the very same list can be inner-joined to drive which Contacts, Opportunities, Cases, etc you load. Also note, this may be time consuming to build in Talend, but it is super easy in a database with some SQL.

0

Children that were born as Orphans can often have more qualities than a Children with Parents at birth. Let me explain...

The easiest way to load records that are in some sort of Parent-Child Hierarchy, assuming that you are not concerned with Audit fields (i.e. LastModifiedBy) during the creation, is to create a 2 step Talend Job with 2 Sub-Jobs:

SUB-JOB 1: Load ALL Records, with as many levels of Hierarchies as you wish, as "orphan" records with Hierarchy (Parent/Child) Lookup fields empty (null) on all records.

Link your Sub-Jobs with a "On Sub-Job 1 OK start Sub-Job 2" trigger (Right click on Sub-Job 1 input > trigger > "On Subjob OK" > link with Sub-Job 2 input)

SUB-JOB 2: Update Lookup fields on all records with correct Parent/Child ID.

This method allows you to skip the sequential load of each hierarchy level before loading child records. Instead, you create ALL records at once and then once all records exist in the system you update the parent-child relation. The only thing that is sequential in this method is that ALL records need to be created BEFORE you start updating the Hierarchy Lookup Fields (Sub-Job 1 needs to finish before Sub-Job 2 has started)

0

Its me again, hi, I wrote the TalenD blog post that you mention. I've got something better now, and it can handle the self-references that you mention -

I've written an Open-Source windows app to copy test data to Sandboxes. Its called SandboxBerry and its on GitHub:

https://github.com/codeulike/SandboxBerry

This is how SandboxBerry works:

  • You give it a list of Salesforce objects (in the form of an XML file)
  • optionally you can specify filters for those objects in SOQL syntax
  • SandboxBerry will then transfer the data for those objects from your Source (usually Production) to a Target (always a Sandbox)
  • While transferring the data, it fixes the following problems:
    • Relationships between objects will automatically be preserved, substituting new Ids for the old Ids
    • Any inactive or missing users will be defaulted to the user specified by you
    • Only the objects fields that are present in both Source and Target will be transferred
  • Optionally, you can skip specific fields or hard-code their values in the Target - this can be useful for anonymisation
  • It can deal with self-referencing objects (but only one self-reference field per object)

Its a tool aimed at developers so you'll need to be happy with editing XML files and knowing the API names for Salesforce objects, but hopefully it will be useful for people.

See the Readme and the wiki on the github repo for more info

You can download the built version from the Github Releases section.

Screenshot:

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.