Data Migration or Data Integration is a very common task in most of the Dynamics CRM deployments. Data migration often becomes complex with the following:
- The diversity of data and systems
- Data integrity
- Time-consuming for large data set
- The complexity and intricacy when working with Microsoft Dynamics CRM web service interfaces
Data quality is often very key to user adoption and lack of it can make the system unusable. The bigger question has always remained,
what tool should we use for quick data migration?
At the very broad level, there are two different approaches available for data migration/integration with Dynamics CRM, namely –
- Leverage existing technologies and tools
- Dynamics CRM Import Wizard
- Extract – Transform – Load (ETL) tools
- Sql Server Integration Services
- Connectors for Dynamics CRM shipped by Microsoft
- BizTalk/Service Bus
- Other tool…
- Write your own tool
In this post I’d discuss Data Migration using SQL Server Integration Services (SSIS). SSIS does not ship any connector for Microsoft Dynamics CRM and for this purpose I’d recommend KingswaySoft connector.
Download the KingwaySoft SSIS Integration Toolkit for Dynamics CRM from here.
KingswaySoft offers a FREE DEVELOPER LICENSE. Under the developer license, one can use the software within development environment (Visual Studio, or BIDS – Business Intelligence Development Studio) for free for as long as one wants.
If you wish to run the SSIS package from SQL Server then you need license key for KingswaySoft. Details about licensing can be found here.
The software pre-requisites to run KingswafSoft connector are following:
- Microsoft SQL Server 2014, 2012, 2008 R2, 2008, or 2005
- .NET Framework 3.5 Service Pack 1
- Windows Identity Foundation (Only required when working with CRM Online or federated environment)
Below is a step-by-step process of using KingswafSoft connector to migrate data to Dynamics CRM:
Step 1: Get your environment ready
- Download “Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio” from Microsoft download site.
- Install the SQL Server Data Tools – Business Intelligence.
- Download the KingswaySoft connector installer from here.
- Install the KingswaySoft connector as described here.
Step 2: Creating Connections
- Open Visual Studio and start a New Project.
- Select “Integration Services” project template.
3. Here, I’ll discuss data migration from a SQL Server database to Dynamics CRM Online instance.
4. The first thing is to create Connection Managers for both SQL Server and CRM Online.
Create Connection Manager for SQL Server
a. Right Click on the Connection Manager‘s area and Select “New ADO.NET Connection“.
b. In the Configure ADO.NET Connection Manager pop-up, click on New and in the Connection Manager screen provide the following details:
1. Server Name
2. Credentials to login to server
3. Select the database or attach a database
c. Now alternatively we can rename the connection to a more friendly name like “Contoso DB“.
Create a connection manager for Dynamics CRM
a. Right Click on the Connection Manager‘s area and Select “New Connection” and in the Add SSIS Connection Manager screen select DynamicsCRM.
b. Provide the following details in the CRM Connection Manager pop-up.
c. Now alternatively we can rename the connection to a more friendly name like “Dynamics CRM“.
Step 3: Creating a Data Flow task
- Click on the “Data Flow” task in the Visual Studio Package.dtsx design window.
2. My SQL DB Contact table has got the following schema & data.
3. Drag a ADO.NET Source and provide the Contoso DB Connection and a SQL Script.
4. Then Add a Dynamics CRM OptionSet Mapping component to the package to map the Marital Status field values.
5. Finally add a Dynamics CRM Destination component.
6. The final package will look something like this:
Step 4: Execute the package
1. Run the package clicking the Start button.
2. Once the package completes execution, it would display the.
Step 5 : Check Dynamics CRM Contact Entity
1. Browse to Dynamics CRM Contact entity and verify if the records are successfully migrated.