Migrating Microsoft Dynamic database using SSIS technology Part 1.

There are various ways for migrating data, you can use the adapters that released by third party, such as kingswaysoft …but it is not suitable for your purpose in some cases, this post will write about basis way which you can implement the migrating data by your own. Before starting, you need to have knowledge in

• SSIS
• Microsoft Dynamic CRM
• .Net

Data migrating concept

The basic concept of migrating data is mapping, transforming and importing data. You can see there are many product which can be used for data integration such as Biz talk server, EDI solution…But all technology will have steps as following image, this is the concept for data migrating solution.

Migrating data concept

Solution

Case study

Migrating Microsoft Dynamic CRM 2011 to Microsoft Dynamic 365.

For real project data migration, you need to check following:

• Order for data migrating.
• Solving wrong data for migrating.
• Investigate the hardware infrastructure

Solution components

The solution will use SSIS technology for migrating data, the components will include:

• Microsoft Dynamic CRM 2011: Source database
• Microsoft Dynamic 365: Target database
• SSIS: data package for migrating
• SQL database: Staging database

Solution details

Defining the staging database

Defining mapping table

Defining staging table for mapping

Each table of staging database will be equivalent to each view/table of Microsoft CRM table. For example, there is 1 account view, we will define 1 table call Staging_Account in Staging database. For taking advantage of automatically mapping of SSIS, you can define the field name of Staging_Account are the same with Microsoft Dynamic CRM.

Each table will contain 1 field: “IsProcessed”, using to keep track status of record.

Note: For master data which are not related to other entities, you can use Microsoft Dynamic Migration tool to transfer data from CRM 2011 to Dynamic 365.

Defining table for configuration and logging

Create 1 table for configuration, called SSISConfiguration, this table will be responsible for:

• The order of package running.
• Database connection.
• Enable/Disable SSIS package.

This article will use account view for sample package.

Creating SSIS package

From Microsoft Dynamic CRM 2011 to staging database

Step 1: Creating 1 SSIS Project, I will name it “SSISPackage_FromCrm2StagingDatabase”

Step 2: Adding 1 package (This sample will use account)

Step 3: Select OLE DB for source &  OLE DB for destination.

Step 4: Drag the connection from source to destination.

Note: We can drag other source destination for error logging to log all error record, in this sample, I will use the script component. 

From Staging database to Microsoft Dynamic 365

Step 1: Creating 1 SSIS Project, I will name it SISPackage_FromStagingDatabase2Dynamic365″

Step 2: Creating 3 variables to get the return values of Execute SQL Task

Step 3: Adding 1 package (This sample will use account)

Step 4: Dragging 1 Execute SQL Task to Control flow tab.

Step 5; Adding SQL command to Execute SQL Task

Note: Select the Result Set is Full result set.

Step 6: Getting configuration of list of packages will be run

Step 7: Adding the Foreach of container to data flow control, this loop will be loop and run your package automatically based on configuration.

Step 8: Mapping values to the variables which created previous step.

Step 9 (Optional): Setting the expression to ensure that data is right, I will check the Action-type to ensure that it does not miss in configuration database.

Step 10: Adding the Expression Task to ForeachLoppContainer.

Step 11: Adding Data flow to your package, then, make connection from expression task to your data flow and then add the condition to your expression. Do the same for other data flow (Update, Assign…)

Step 11: Creating data flow for migrating data, select your data flow and edit.

• Adding 1 OLE DB and select the staging database.
• Adding 1 Script component as destination, you also can select as transformation type.

After these steps, we will have the structure package for migration data base on SSIS technology. I will continue writing about script component implementation in next post.

Hope that it will be useful for someone.