Today's blog post was written by Rob Jasinski, Principal Developer at Sonoma Partners.
There are many occasions when you’ll need to migrate a large amount of data from some outside data source into your Dynamics CRM or Salesforce environment. Both systems do have native import tools, but they have many limitations and can be difficult to use on a large amount of data. If your company owns SQL Server licenses, those also come with SSIS licenses and that can be a viable alternate solution to migrate data into CRM. This article will give you an introduction to setting up a simple data migration to get you started for creating your own CRM migration solutions.
First you’ll need SQL Server Data Tools. This can be downloaded from this link. You should have Visual Studio installed, but if you don’t this will also install a light version, enough to run data tools and allow you to create SSIS integration package.
Next, since you’ll be migrating data to CRM, you’ll need a third party destination component. Now you can write your own custom code, either in C# or VB, and code directly to the API of the destination CRM system, but this can be tedious and very time consuming. These third party tools encapsulate all the code into easy to use drag-and-drop components. They are very reasonably priced and will save a lot of time and frustrations. There are many third party components you can use from vendors such as CozyRoc, CData, and KingswaySoft. For this example, we’ll be using KingswaySoft, but the other vendors would work fine as well. You can download the component used in this example from this link.
Once you have data tools installed, launch Visual Studio and choose new solution and choose “New SSIS Package."
This will create an empty canvas ready for you to start creating your first SSIS data migration.
Next you’ll need to create a data source. This is the location of the source data that exists outside of your current CRM solution. SSIS has several options to pull data from many sources, including Excel, SQL Server, ODBC, CSV files, etc. For this example, we’ll assume our data is located in a SQL Server database. So in the Connection Managers area, right click in that area and choose “New OLE DB Connection.” Configure the server name, credentials, and database name, and click OK. In this example, I’ve renamed the connection to “Source.”
Next drag and drop the “Data Flow Task” from the SSIS Toolbox onto the Control Flow canvas.
Double-click the Data Flow Task just created to open the Data Flow canvas. This is where you’ll create the actual migration using source, destination, and transformation components (if needed).
Next drag and drop the OLE DB Source component from the SSIS Toolbox onto the Data Flow canvas and configure the component along with the query to pull the data that you need. In this example, we’ll be pulling basic account information from some outside system into our Dynamics CRM environment.
Now you’ll need to configure a Destination connection. In this example, we’ll be using the KingswaySoft Dynamics CRM adapter for connecting to our CRM environment. Right click in the Connection Managers area and choose “New Connection.” From the Connection list, choose DynamicsCRM and click Add. Also, you must have already installed the KingswaySoft Dynamics CRM adapter for it to appear in this list.
Next you’ll need to configure the CRM connection. Choose the appropriate Authentication Type, depending if you’re online or on-premise. Then enter the CRM Discovery URL, then any credentials and finally choose the appropriate organization name from the drop-down list. Since there are many environment configurations you may be using, please refer to this KingswaySoft documentation for further details on configuring this.
You should now have two connection managers. In this example, I’ve renamed it to DynamicsCRM. Most migrations you create will mostly like just have two data sources, one source and one destination. But there may be cases where you have multiple sources that are then merged together within the Data Flow process before migrating to CRM.
Next drag and drop the Dynamics CRM Destination components from the SSIS Toolbox onto the Data Flow canvas. Then manually connect the arrow from the OLE DB Source to the new CRM Destination just created. The component has a little red X displayed because it still needs to be configured in the next step.
Next double click on the Destination component to bring up the configuration screen. Choose the CRM connection manager you just created. For this example, we’re migrating data into accounts, so choose that as the destination entity.
Next select the Columns tab and map the fields from the source query to the fields in CRM. Using a third party component, like KingswaySoft, it’s that easy and no custom coding is required.
Press OK and you’re done. You’ve created your first, although very simple, data migration of data outside of CRM into CRM. Now the nice thing about SSIS, it comes with many transformation tools out of box that allow you to cleanup, manipulate, or even de-dupe data. For example, here is a link that is a more complicated example of an SSIS data migration that also de-duplicates data. So from this simple example as a starting point, you can expand it to perform almost any kind of complicated data migration your business may require.