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.

Archive

November 2024 3 October 2024 1 September 2024 1 August 2024 2 July 2024 1 June 2024 1 May 2024 1 April 2024 2 March 2024 2 February 2024 2 January 2024 2 December 2023 1 November 2023 1 October 2023 2 August 2023 1 July 2023 2 June 2023 1 May 2023 2 April 2023 1 March 2023 1 February 2023 1 January 2023 2 December 2022 1 November 2022 2 October 2022 2 September 2022 2 August 2022 2 July 2022 3 June 2022 2 May 2022 2 April 2022 3 March 2022 2 February 2022 1 January 2022 2 December 2021 1 October 2021 1 September 2021 2 August 2021 2 July 2021 2 June 2021 1 May 2021 1 April 2021 2 March 2021 2 February 2021 2 January 2021 2 December 2020 2 November 2020 4 October 2020 1 September 2020 3 August 2020 2 July 2020 1 June 2020 2 May 2020 1 April 2020 1 March 2020 1 February 2020 1 January 2020 1 December 2019 1 November 2019 1 October 2019 1 May 2019 1 February 2019 1 December 2018 2 November 2018 1 October 2018 4 September 2018 1 August 2018 1 July 2018 1 June 2018 3 April 2018 3 March 2018 3 February 2018 3 January 2018 2 December 2017 1 April 2017 1 March 2017 7 December 2016 1 November 2016 2 October 2016 1 September 2016 4 August 2016 1 June 2016 1 May 2016 3 April 2016 1 August 2015 1 April 2015 10 August 2014 1 July 2014 1 June 2014 2 May 2014 2 February 2014 1 January 2014 2 October 2013 1 September 2013 2 August 2013 2 June 2013 5 May 2013 2 March 2013 1 February 2013 1 January 2013 1 December 2012 2 November 2012 2 September 2012 2 July 2012 1 May 2012 3 April 2012 2 March 2012 2 January 2012 1

Tags