21 Jul’15

Dynamics CRMCRM Online Migration using SSIS

In this Blog we will walk-through the migration of Data from Microsoft Dynamics CRM (Source) to Microsoft Dynamics CRM (Target) 2015 using SSIS. We would be using SSIS Integration Toolkit for Microsoft Dynamic CRM by Kingsway Soft.

We would be referring SSIS Integration Toolkit for Microsoft Dynamics CRM by Kingsway Soft as Kingsway-SSIS Toolkit. This toolkit provides 4 major components for Integration and migration of Data.

  1. Microsoft Dynamics CRM Connection Manager
  2. Microsoft Dynamics CRM Source Component
  3. Microsoft Dynamics CRM Destination Component
  4. Microsoft Dynamics CRM OptionSet Mapping Component

Pre-Requites:

This Toolkit is supported on below versions of Microsoft Dynamics CRM and Microsoft SQL server.

.NET Framework 3.5 SP1

Microsoft Dynamics CRM
Microsoft Dynamics CRM Online
Microsoft Dynamics CRM Server 2015
Microsoft Dynamics CRM Server 2013
Microsoft Dynamics CRM Server 2011
Microsoft Dynamics CRM Server 4.0
Microsoft Dynamics CRM Server 3.0

SQL Server
Microsoft SQL Server 2014 (since v5.0)
Microsoft SQL Server 2012
Microsoft SQL Server 2008 R2
Microsoft SQL Server 2008
Microsoft SQL Server 2005

Other Tools that provide the same integration as Kingsway Soft are as below:
Cozyroc.com

  1. http://cozyroc.com/ssis/dynamics-crm-source
  2. http://cozyroc.com/ssis/dynamics-crm-destination

PragmaticWorks.com

  1. https://pragmaticworks.com/Products/Task%20Factory/Features/DynamicsCRMSource
  2. https://pragmaticworks.com/Products/Task%20Factory/Features/DynamicsCRMDestination

Work Flow:

  1. After Installation of Toolkit, below options are visible in SSIS Toolbox in SSDT (SQL Server Data Tools).

1

A new connection can be added in Connection Manager as below for DynamicsCRM.

2

Information needs to be provided to connect to CRM server as below.

3
(http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm/help-manual)

  1. The Authentication Type option allows us to specify the type of CRM server (deployment) and what authentication is used. There are four options available.
  • Active Directory (On-Premise)
  • Federation (IFD, On-Premise or Partner-hosted)
  • Online Federation (Office 365 CRM Online)
  • LiveId (Legacy CRM Online)
    1. The CRM discovery server can be found in the CRM system from Settings → Customizations → Developer Resources after logging in to your CRM application. Service endpoint gets auto populated.
    2. You can specify the Timeout in seconds. Default is 2 minutes.
    3. On providing the User Name and Password, Organization gets populated in drop down list. Select the Organization name and test the connection. Once the connection is successful. Click Ok. If you connectivity is through Proxy server you can specify the connection details for the same in the second tab Advanced Settings. It is recommended to have the box ticked-on for Retry on Intermittent Errors.
    4. Follow the same steps as above for creating Destination connection for Target CRM where the data is to be migrated.
    5. 4

    6. Launch SSDT (SQL Server Data Tools and select Integration Project and drag and drop the Source and the Target. Here in this example we have not performed any data or file transformations. It’s simply connecting to CRM source and pushing the data to Target CRM based on mappings specified for entities as well as Text Lookups specified if any.

      5

      In this example we are migrating Entity – Accounts.
      6

    CRM source Connection name in this example is named as “Source” and CRM Destination connection as “Target”.
    7

    8

    Using this toolkit, in the columns Tab, Most of the columns gets auto-mapped.  Verify the mappings, specifically the ones with Text Lookup. Action gives the options like Create, Update, Delete, Upsert, Merge, Convert and Execute Workflow. We need to select Create if we are simply migrating the data into target CRM (History Load). Select the Destination entity. Upsert Settings are applicable only if Upsert Action is selected.

    Choose Target Fields. Target Entity gets auto populated. Using the drop down list select Target text/ Integer Field. Advanced Options can be left unselected. Cache strategy needs to be selected with Performance and size of data that needs to be migrated. (This is similar to Cache mode in SSIS look-up transforms).
    9

    Now the package is ready to be executed. Execute the package and verify by logging into CRM, all the fields are populated.

    Note: As a general practise during SSIS package creation, sequence containers are used for each entities that are to be migrated. Only one source and destination CRM connection needs to be created.

    Benefits of Parallelism can be used for performance benefits as well as individual tasks (for single entity) can be selected in the data flow to be executed, with others as disabled.

    Written by

    Team Member

    CloudFronts

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Time limit is exhausted. Please reload the CAPTCHA.

    Want to streamline your business processes?

    • This field is for validation purposes and should be left unchanged.

    Recent Articles

    • Reading more then 10K records in D3FOE OData API

      11 July’ 2018

      Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

      Read more
    • Paging in D365 Customer Engagement v9.0

      10 July’ 2018

      Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

      Read more
    • Set up Dynamics 365 connection in Microsoft Social Engagement

      10 July’ 2018

      Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

      Read more
    • Voice of the Customer failed to install

      10 July’ 2018

      Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

      Read more
    • Scribe Insight AX as a Web Service Find Block issue

      10 July’ 2018

      Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

      Read more