Migrating Data Between CRM Online Instances using SSIS

Author by Concurrency Blog

Recently I've been learning some SSIS as practice for an upcoming client need. I found many resources on migrating data from a SQL Server database to CRM Online/On-Premise, but no resources on migrating between CRM Online instances. I think this is an important use case for organizations that use CRM Online, so I decided to take a closer look to see how this could be accomplished. Suppose you have to migrate account or contact data from one CRM Online instance (i.e. Prod) to another (i.e. Dev). This can be achieved through SSIS using two script components. Below are some instructions on setting this up. In my case, I am working with two CRM Online (2013) environments.

Instructions

Start-up SQL Server Data Tools and create a new Integration Services project. In the Control Flow tab of the Package design view, add a new Data Flow Task, then double click it. dataflowtask
Now you should be on the Data Flow tab. Add a Script Component, and select Source for script type. Click OK. Then double click the Script Component that you created to edit it.
selectscipttype_source
On the Script Transformation Editor screen, click Inputs and Outputs. Rename Output 0 if desired (will have to change it in the code as well). Click the Output Columns folder. From here, add all the columns that's values must be migrated to the destination CRM instance. Their names don't have to match the CRM schema names, but should be recognizable when coding them in later.
 
scripttanseditor1 By default, the output columns have a data type of four-byte signed integer. Depending on your columns, you might want to change this.
 
Now go back to the Script section and click Edit Script...
 
scripttranseditor_backtoscript
A new Visual Studio window will pop up, titled VstaProjects. In the Solution Explorer, add the following references to VstaProjects. The first three can be found in the CRM 2013 or 2015 SDK bin folder; the second two can be found in the .NET tab of the Add Reference window.
 
  • Microsoft.Crm.Sdk.Proxy
  • Microsoft.Xrm.Sdk
  • Microsoft.Xrm.Tooling.Connector
  • System.Runtime.Serialization
  • System.ServiceModel
Once the assemblies are added, on the main.cs, make changes similar to the below (outlined in red). In my case, I was migrating account data, so your screen will look different depending on your situation.

maindotcs_source_1

maindotcs_source_2

 

Save your changes and close the VstaProjects window. Then click OK on the Script Transformation Editor screen.

On the Data Flow tab, create another script component, and place it below the first one. Select Destination for script type and click OK. Click the source Script Component you created before, then drag the arrow down to the destination Script Component. Double click the destination Script Component to edit it. On the left side of the window, click Input Columns and select all of the columns you created before. Now click Inputs and Outputs. The input columns should be automatically added. Click Script on the left and then the Edit Script... button. In PreExecute(), make changes similar to the ones you made in the source script, just make sure you change the proxy uri to the destination CRM instance. Then, in the ProcessInputRow method, enter the following (or similar, based on your situation): maindotcs_dest Save and close the project window, then click OK on the script transformation editor screen. Save and build the main SSIS project. Press F5 to start debugging.

Common Issues

Some issues you may run into include the following:
  • Script error - "Could not load file or assembly "Microsoft.Xrm.Sdk..". Make sure the DLL specified in the error message is present in the GAC (C:Windowsassembly OR C:WindowsMicrosoft.NETassembly).
  • Script error - "The given key was not present in the dictionary". Make sure the schema names you are providing (i.e. a.Attributes["schemaname"]) are accurate.
  • Script Component changes not saved. Sometimes the script component changes are not persisted. For a fix click here.
  I hope this post helps you setup your CRM data migration successfully. Good luck!  
Author

Concurrency Blog

The latest about Concurrency