Migrating Business Units using SSIS Integration Toolkit for Microsoft Dynamics CRM

25 June 2013
Daniel Cai

Business unit is an important entity in Microsoft Dynamics CRM. Migrating or replicating business unit data is an important step in ensuring a successful CRM data migration.

In this blog post, we will demonstrate how to migrate business unit data with the following two objectives.

  • Maintain the business unit hierarchy in the target system as the same as the source system
  • Retain the same business unit ID (GUID) values in the target system as the source (except the root business unit)

The root business unit is an exception of the above objectives, since it is automatically created at the time when CRM organization was provisioned. Once the root business unit is created, there is no way to change the ID (GUID) value of it primary key (businessunitid field). In other words, the root business units would always have different ID for two different CRM organizations.

With the above two objectives in mind, we can utilize the Text Lookup feature to make the data flow simple. In case you don't know, Text Lookup is one of advanced features offered by SSIS Integration Toolkit for Microsoft Dynamics CRM, which helps manage lookup references in an easy way so that you can pass in text value to a CRM lookup field and the component will automatically lookup the target CRM record's ID by matching the text value.

Since business unit is a self-parenting CRM entity, so we would have to design the data flow task in the way that it runs in a two-pass fashion.

  • In the first pass, we load all business units (except the root one) by setting their parent business unit to the root business unit of the target system (parentbusinssunitid is a required field). 
  • In the second pass, we re-parent all business units by setting their parent business unit to the right business unit (Re-parenting). 

To make the data flow simple, we would like to utilize the CRM destination component's error handling mechanism to design SSIS data flow, as all we need is one dataflow task with two CRM destination components, so it might be something like this.

Looks simple, right? It does, however this won't work, as there is one more thing that we have not properly addressed in this data flow. We need to make sure that the second pass is not started before the first pass is 100% completed, otherwise, the Lookup reference would most likely fail for the second destination component, as there might be scenarios that a child business unit could be loaded before its parent(s) in the first destination component.

Alternatively, you can design your SSIS packages so that you have two data flow tasks that run in a sequential fashion.

In order to be able to keep our data flow task simple by utilizing only one data flow task, we would have to find a way to make sure the second component doesn't start loading data until the first component is 100% completed.

The rescue is utilizing a SSIS fully-blocking asynchronous component. One of such components is SSIS Sort Component. So the revised data flow would look like this.

The key difference is, we added a Sort component before the second destination component. With the new data flow, you should be able to perfectly load all business unit records while maintaining the same hierarchy in the target system and retaining all ID values.

Design Patterns

In this blog post, we have demonstrated a few techniques and design patterns that you might useful when using SSIS Integration Toolkit.

  • Error Handling and Logging within Data Flow
  • Multi-destinationed Data Flow
  • Fully-blocking Flow-through
  • Text Lookup
  • Upsert

As mentioned, in this data flow, we have also used Upsert functionality offered by SSIS Integration Toolkit so that this package can be used for ongoing integration to synchronize business unit records between two CRM organizations at any time as necessary.

Sample SSIS Package

To help make it easy to understand, I have uploaded a sample SSIS package that includes both a sample script task and script component, which you can try out by downloading from the link below.

If you want to run the SSIS package in your environment, the first thing you need to do is to update the "RootBusinessUnitNameInTargetSystem" variable in the package by changing it to the root business unit of your target CRM organization. Of course, you would also need to update the two connection managers in the packages by pointing to your CRM organizations.

We implemented this SSIS package while assisting one of our clients.

Have a question?

Should you have any questions about the practice discussed in this blog post, please feel free to contact us, we are more than happy to assist you.

comments powered by Disqus