Every now and then, we get the requirement to move data from legacy systems to Microsoft Dynamics CRM. Not all the legacy data have unique primary key fields defined for the tables storing the data. The fact is that some old systems and software do not even have accounts and contacts linked to each other.
In Dynamics CRM, accounts can be associated with a primary contact and contact can have a parent customer defined. In other words, accounts are linked to contacts and vice versa. What would you do if you are migrating disparate accounts and contacts to Dynamics CRM and also have to link them? Well, doing it manually would be too time consuming and not possible if there is large volume of data. In this case let us consider that the account and contact information is coming from the same source say a flat file.
The goal here is to highlight a cool feature of Scribe Insight which would make the life of the SSIS technician too simple. Having said that, I am not going to describe in detail how to set up the source and destination and steps in Scribe which is not the purpose of this article. My assumption would be that the source and destination is defined and steps configured and data fields mapped properly.
To meet our requirement, I suggest creating two destination steps with fields combined to create some type of composite primary key if there is none.
Step1: Update/Insert Account
Step2: Update/Insert Contact.
Now that we have source and destination defined, and the data mapped, let us first define a variable that we will use to store the accountid guid from step1. We will need the accountid to assign to the Parent Customer Field when we update/create a contact record.
Here is how you do it: From the top menu, click on View User Variables. You will get following dialog:
Click on Add Target which will give you this dialog:
Name the variable as AccountGUIDFromStep1 and the select Step as type and pick Step1 as Data Object/Step. In this case Step 1 is Adapter for Dynamics CRM 2011.account Update/Insert and select the field accountid. Now for each account record updated/created, the accountid guid will be assigned to this variable so that we can use it to assign it to the contact record next.
As shown in the screen shot above, select step2 ( in this case Adapter for Dynamics CRM 2011.contact Update/Insert) and then highlight parentcustomerid field and click on Formula button. Once the Edit Formula dialog comes up, click on Show User Variables. This will open a Variables dialog. Pick the variable that we created earlier i.e. AccountGUIDGromStep1 and click Insert in Formula.
You have finally assigned the accountid to the parentcustomerid field of the contact record. Now, that you have assigned the parentcustomerid field for contact record, we also have to set the parentcustomeridtype field to 1 indicating that the parentcustomerid field we just assigned is of type account. Now, I will show you how to update the Primary contact field of the account record updated/created in step1. Scribe has made it so easy to do it.
Go to step2 if you are not in already. Scroll down until you see a field named vfPrimary. This field, when set to true, would go back to the step1 and update the primarycontactid field of the account record with recently updated/created contact record GUID. To set this flag to true, select vfPrimary and then click Formula button and then type “Y” in the Edit Formula box and click OK and save the package.
Now, we are done configuring the steps. Run the package. Accounts will be assigned the primary contact and contacts are assigned the parent customer as shown below:
Wasn’t it so easy? Please comment how this works for you.