Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Dynamics CRM Using Scribe Insight and an Easy Way to Link Accounts and Contacts when Migrating Data

Post Author: Joe D365 |

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:

Dynamics CRM Using Scribe Insight

Click on Add Target which will give you this dialog:

Migrating Data with Scribe

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.

Now let us go to Step 2 and assign this variable value to the contacts parentcustomerid field.CRM and scribe

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.

CRM 2011 and Scribe

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.

Microsoft Dynamics CRM and SSIS

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:

Microsoft Dynamics CRM and Scribe

Wasn’t it so easy? Please comment how this works for you.

Happy CRM’ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

2 comments on “Dynamics CRM Using Scribe Insight and an Easy Way to Link Accounts and Contacts when Migrating Data”

  1. Thank-you for the detailed instructions, I hit a roadblock though where after everything is lined up it will not work because this contains "Update/Import" steps so the test and run options are grayed out. It would only work as "Insert" steps because of there are no look-ups in your walk-through. Any ideas what I am doing wrong?

  2. Using a target variable is one method for setting the value of the parentcustomerid field on the contact. However, Scribe automatically assigns that value using the auto foreign key assignment when inserting child records (the contact in this example), so you usually don't need to use a target variable.
    Also, in this case, you need the Update/Insert step for the account (assuming there can be multiple contacts per account), but only an Insert step for the contact (assuming that each contact is in the file once).

PowerObjects Recommends