Migrating Microsoft Dynamic database using SSIS technology Part 2

Continuing  the topic for data migration in Microsoft Dynamic, this article will write in details of each components, you can read the high level design at this article. 

From CRM (old version) to new version package.

As the explanation before, we create 1 package to migrate data from Dynamic Crm old version to staging database. The package will include the components as following image.

• 1: Data flow to generate the batchid to track for package running and history tracking.
• 2: Main data flow to migrate data to staging database.
• 3: Update executed end time of package.

We should create master variables for database connection string, migration component, batchid.

Generate batchid data flow

1. Select “Generate batch id” data control flow, and add script task.
2. Click edit script to open the code

Declaring master variables

    #region PARAMETERS
   //batchid is output parameter
    int batchId = 0;
    string strConnectionString = string.Empty;
    string strPackageName = string.Empty;
    #endregion

At PreExecute method, init the values of master variables.

public override void PreExecute()
{
base.PreExecute();
/*
* Add your code here
System::PackageName,
User::StagaingDbConnection,
System::StartTime
*/
try
{
string strStagingDbConnection = string.Empty;

strPackageName = ReadOnlyVariables[0].Value.ToString();

if ( ReadOnlyVariables[1].Value.ToString().TryDecrypt( out strStagingDbConnection ) == true )
strConnectionString = strStagingDbConnection;
else
strConnectionString = ReadOnlyVariables[0].Value.ToString();
}
catch (Exception ex)
{

throw ex;
}
}

At CreateNewOutputRows method, make the connection to staging database and create batchid, then set the output is value of new batchid.

public override void CreateNewOutputRows()
{

try
{
Output0Buffer.AddRow();
//I used my internal library, cannot be shared. you can follow step:
//1. Make connection to staging database.
//2. Create new batchId
if ( batchId > 0 )
Output0Buffer.BatchId = batchId;
else
throw new Exception( "BatchId cannot be created, please contact Administrator!" );

}catch(Exception ex )
{
throw ex;
}

}

Migrating data to staging database flow.

The data flow will be designed as following image.

Data will be select from dynamic database and insert to staging database, if any errors happened, the error record will be logged in Exception log table included the batchid.

1. Setting up data source

2. Setting up destination

For testing, you can remove the step for logging error. I will write about seperate the log error in other article, it will be in details in the step from staging to new dynamic system.

Hope that it will help some one.