SSIS and CRM Series, Part 2 - Use SSIS to Copy CRM Configuration Data from One Environment to Another

This is part 2 of Blog Series - Better Together, SSIS and Microsoft Dynamics CRM.

Scenario Summary

In CRM projects, we often rely on configuration data to implement business logics. One of such scenarios is CRM workflow. It is very common that we use lookup reference in CRM workflows to implement business rules.

The following is a sample workflow that fires when a new CRM account is created. The workflow will create a task record when the CRM account record belongs to country USA (Country in this case is a custom entity, which has a 1:N relationship to CRM account entity).

All is good. We have the workflow developed in our development (DEV) environment. After your verification is done in DEV, you will start to promote the changes to your testing or production environment. That should be an easy job with the help of CRM solution framework, you would say. However, this might not be the case exactly. After your solution is imported into your target environment, you will then realize that your workflow isn't working. You are getting an error message saying "The process includes an invalid reference", shown as the following screen.  

As indicated by the error message, the problem is rather simple, your workflow is using a lookup reference, but the referenced CRM record (the USA country record in our case) doesn't exist in the target system.

If you have not worked long enough with Microsoft Dynamics CRM, you might be wondering to just simply create the CRM record(s) in the target system from CRM user interface using the same values that you have from the source system, and expect the workflow to work. You will soon realize that this will actually not work, CRM workflow won't use the record(s) that you have manually created. The reason is, as far as CRM reference goes, CRM only recognize a record's ID in GUID format, not the primary field value or values from any other fields.

So, how can we fix this problem?

The answer is quite simple too, you basically have two solutions. The first solution is to manually fix the reference in the target system one by one, by opening each workflow in its designer. The second option is to find a way to copy (migrate, transfer, or whichever terminology that you may think appropriate) such CRM configuration data to the target system by the same IDs along with the values from other fields. The choice would be obvious, the second one would be a much better option if there is an easy to do so.

Assuming that you have decided to go with the second option, you would then be looking for tools that can help copy configuration data from one environment to another. There are a number of ways to do this, you can possibly use CRM native tools including its exporting tool and CRM Data Import Wizard to first export data from source system and then import to the target system. However, there are a number of limitations if you use CRM export and import tools, and there is almost no way to manage incremental changes. You would soon started blame yourself if you decided to go this path. Alternatively, you can write C# (or VB) code using CRM SDK to export data from source system and import to the target one. This is not an easy job either, as it takes effort to write code, especially it becomes quite challenging when you need to make it generic enough in order for the tool to support more than one entity. My previous preference was CRM Configuration Data Utility, which is a utility built for CRM4, and it is a very handy tool to handle this situation. I have used the utility extensively in my previous CRM projects.

With the availability of SSIS Integration Toolkit, we can be even more productive when it comes to this business scenario.

Let's get into action to see how this can be done using SSIS Integration Toolkit.

Prerequisites

You need to have a system that has the following components installed.

• Business Intelligence Development Studio component from either SQL Server 2005,  SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012
• SSIS Integration Toolkit for Microsoft Dynamics CRM
• Windows Identity Foundation

Develop SSIS Data Flow

• First, you would launch Business Intelligence Development Studio to create a new SSIS project, and then create a new SSIS package within the project. 
• You would need to create two Microsoft Dynamics CRM connection managers that connect to your source system and target system.
  
• In the SSIS package, create a new data flow. 
• Within the data flow, create a new CRM Source Component that reads data from the source system by choosing the entity that you want to copy data from. In our case, the entity we are working with is called new_country, your entity might be something different. 
• In the data flow, create a new CRM Destination Component, and connect it to the CRM Source Component that we have just created. The CRM destination component will be used to write data to the target system. In the destination component, you would choose the same CRM entity as the source component, select Upsert as the Action option, and Primary Key as Upsert Matching Criteria option. Also make sure to select "Remove Unresolvable Reference" option.
  
• After you have finished developing the data flow (which should only take you a few minutes if you are not new to SSIS), you can start to run the data flow to copy data from your source system to target system (As shown below, I have copies 273 records with one single click).
  

Some Final Notes

• The data flow can be reused, so whenever you have made any further changes in your source system, you can simply fire the SSIS package and run it again, your target system will have all the latest configuration data. 
• In this blog post, I have shown you one typical problem that you need to deal with when working with Microsoft Dynamics CRM. SSIS Integration Toolkit is not limited to any particular entity, you can copy data from one environment to another, for any entity that can be created and updated by CRM SDK through its web service interface. 
• I have skipped some basic details in this blog post about how you create SSIS project, how you add connection managers, and how you add CRM components to your SSIS toolbox. All such details can be found in part 1 of this blog series or the product's help manual page. 
• SSIS Integration Toolkit is a commercial product, and I work for KingswaySoft, the provider of the tool. 

Hope this helps.