4,755
Like
Share
ReportPrevious posts have discussed about detail solution for migrating data from CRM 2011 to Dynamic 365, the summary of solution based on SSIS and Sdk of Dynamic 365 only, it will save cost to purchase third party solution…This solution adapt for On-premise system only.
You will need the basic knowledge of SSIS to understand, this post is out of scope to explain detail the structure and knowledge of SSIS.
First post has been written for solution to move CRM 2011 to staging database, this post will write in detail to move data from staging database to D365 database.
Step 1: Create SSIS package
Create the package as following image.
Control flow will be controlled by Staging Database as explained in part 1, all the configuration of SSIS package running will be contained in staging table, including running order, activate/deactivate, message, mapping…
Basing on migration configuration, we can set condition to run the data flow of SSIS package.
Step 2: Design Data flow for Create Message.
Design SSIS data flow as following image, using 1 OLE DB Data Source and 1 Script Component.
Define the query to query what attributes we need from staging database.
Step 3: Migrating Data to D365
Select the Script component, right click to select the edit script. You will see as following:
We need to add reference D365 library and other if need ( depend on your design ), in my case, I use my own library and LinQ, these library need to be registered in GAC of SSIS server later for deployment.
Step 4: Init mapping file and create batchId
We will init all needs in PreExecute, all things have to do:
- Init all master variables.
- Serialize mapping file
- Get Attributes Information
- Generate batchId for tracking
public override void PreExecute ( )
{
base.PreExecute();
/*
* Add your code here
*/
try
{
#region Init: Master variables
/*
* Variables: Value & Index of Readvariable
0 User::ActionType,
1 User::BatchId,
2 User::CrmConnectionString,
3 User::MappingFile,
4 System::PackageName,
5 User::StagingDbConnectionString,
6 System::StartTime,
7 User::UserName
* 0: User::ActionType
* 1: User::BatchId
* 2: User::CrmConnectionString
* 3: User::Mapping File
* 4: System::PackageName
* 5: User::StagingDbConnectionString
* 6: System::StartTime
7: User::UserName
*/
// 0: User::ActionType
strActionType = ReadOnlyVariables[0].Value.ToString();
//1: User::BatchId
batchId = int.Parse( ReadOnlyVariables[1].Value.ToString() );
//2: User::CrmConnectionString
strCrmConnectionString = ReadOnlyVariables[2].Value.ToString();
//3: User::Mapping File
strMappingFilePath = ReadOnlyVariables[3].Value.ToString();
// 4: System::PackageName
strPackageName = ReadOnlyVariables[4].Value.ToString();
//5: User::StagingDbConnectionString
strStagingDbConnectionString = ReadOnlyVariables[5].Value.ToString().Decrypt();
// 6: System::StartTime
dtExecuteStartTime = DateTime.Parse( ReadOnlyVariables[6].Value.ToString() );
#endregion
#region Steps: serialize mapping file
if ( File.Exists( strMappingFilePath ) )
{
var document = new XmlDocument();
document.Load( strMappingFilePath );
objMapping = (Mapping)XMLSerializeHelper.funDeserialize( document.OuterXml, typeof( Mapping ) );
if ( objMapping == null )
throw new Exception( "Cannot Read From Local" );
}
else
{
SqlConnection con = new SqlConnection(strStagingDbConnectionString);
con.Open();
SqlCommand comm = new SqlCommand(@"select MappingXML from MappingXML where packagename = 'Account_From_StagingDB_To_365' ");
comm.Connection = con;
XmlReader reader = comm.ExecuteXmlReader();
var document = new XmlDocument();
if ( reader.Read() )
document.Load( reader );
objMapping = (Mapping)XMLSerializeHelper.funDeserialize( document.OuterXml, typeof( Mapping ) );
con.Dispose();
}
if ( objMapping == null )
throw new Exception( "SSIS package failed to deserialize mapping file!" );
lstEntities = new List<Entity>();
#endregion
#region Init: Connection string of Crm server & Staging database
#endregion
#region Get: All Colums Info
var ColumnCount = ComponentMetaData.InputCollection[0].InputColumnCollection.Count;
lstColsInfo = new List<ColumnObject>();
int[] ColumnIndexes = GetColumnIndexes(ComponentMetaData.InputCollection[0].ID); // Same as InputID in ProcessInput
int columnIndex = 0;
foreach ( IDTSInputColumn100 item in ComponentMetaData.InputCollection[0].InputColumnCollection )
{
ColumnObject objColumns = new ColumnObject();
objColumns.ColumnIndex = ColumnIndexes[ComponentMetaData.InputCollection[0].InputColumnCollection.GetObjectIndexByID( item.ID )];
objColumns.ColumnName = Convert.ToString( item.Name );
//GET Migrated colums only
var lstCols = (from l in objMapping.Attributes.Attribute
where l.SSISAttributeName.ToLower() == item.Name.ToLower() || l.SourceAttributeName.Text.ToLower() == item.Name.ToLower()
&& bool.Parse(l.SourceAttributeName.IsMigrated) == true
select l).FirstOrDefault();
if ( lstCols != null )
lstColsInfo.Add( objColumns );
columnIndex++;
}
#endregion
#region Create: BatchId
MigrationUltilities paUltilities = new MigrationUltilities();
if ( !string.IsNullOrEmpty( strStagingDbConnectionString ) )
paUltilities.funOpenConnection( strStagingDbConnectionString, true );
else
throw new Exception( "Cannot open the connection to database." );
if ( !string.IsNullOrEmpty( strPackageName ) )
{
batchId = paUltilities.funGenerateBatchId( strPackageName, "Account", DateTime.Now );
if ( batchId == 0 )
throw new Exception( "Cannot create the batchId." );
}
#endregion
}
catch ( Exception ex )
{
LogErrorInException( batchId, Guid.Empty.ToString(), "Error in Pre, details: " + ex.Message );
throw ex;
}
}<span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span>
Step 5: Mapping data value to new system
- Mapping data to D365
- Generate value base on data type of old system
- Correct/Clean data if need.
We will do it in Input0_ProcessInputRow method of SSIS package.
public override void Input0_ProcessInputRow ( Input0Buffer Row )
{
/*
* Add your code here
*/
string colSourceName = string.Empty;
try
{
Entity objNewRecord = new Entity(objMapping.EntityName);
for ( int j = 0; j <span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span><span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span><span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span><span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span><span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span><span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span>< lstColsInfo.Count; j++ ) { colSourceName = lstColsInfo[j].ColumnName; var cols = (from c in objMapping.Attributes.Attribute where c.SSISAttributeName.ToLower() == lstColsInfo[j].ColumnName.ToLower() || c.SourceAttributeName.Text.ToLower() == lstColsInfo[j].ColumnName.ToLower() && bool.Parse(c.SourceAttributeName.IsMigrated) == true select c).FirstOrDefault(); if ( cols != null ) { var colsValue = Row.GetType().GetProperty(cols.SSISAttributeName); var isColNull = IsColumnNullValue(Row, inputBuffer, lstColsInfo[j].ColumnIndex, colsValue); if ( !isColNull ) GenerateInputRecord( Row, Row.GetType().GetProperty( cols.SSISAttributeName ).Name, objNewRecord, cols, lstColsInfo[j].ColumnIndex ); } } objNewRecord.Id = Row.AccountId; lstEntities.Add( objNewRecord ); } catch ( Exception ex ) { LogErrorInException( batchId, Row.AccountId.ToString(), "Error in Input0_ProcessInputRow, field name: " + colSourceName + " " + ex.Message ); throw ex;
}
}
GenerateInputRecord method
private void GenerateInputRecord ( Input0Buffer Row, string colSourceName, Entity objNewRecord, <span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ><span data-mce-type="bookmark" id="mce_SELREST_end" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span></span>Attribute result, int colsIndex = 0 )
{
if ( result != null )
{
var colsValue = Row.GetType().GetProperty(colSourceName);
switch ( result.DataType.Type )
{
case "Text":
if ( colsValue.PropertyType == typeof( string ) || ( colsValue.PropertyType == typeof( String ) ) )
objNewRecord[result.TargetAttributeName.ToLower()] = colsValue.GetValue( Row, null );
break;
case "Guid":
//Set Primary of entity is the same with old GuidId
if ( bool.Parse( result.PrimaryKey ) )
{
if ( colsValue.PropertyType == typeof( Guid ) )
{
var itemValue = colsValue.GetValue(Row, null);
if ( itemValue != null && new Guid( itemValue.ToString() ) != Guid.Empty )
objNewRecord[result.TargetAttributeName.ToLower()] = new Guid( itemValue.ToString() );
}
}
break;
//Check source data is not equal to null
case "Picklist":
if ( colsValue.PropertyType == typeof( int ) || ( colsValue.PropertyType == typeof( Int32 ) ) )
{
int itemValue = (int)colsValue.GetValue(Row, null);
var op = (from o in result.DataType.OptionSetMappings.OptionSetMapping
where o.OldValue == itemValue.ToString()
select o).FirstOrDefault();
if ( op != null )
{
int optionSetValue = -1;
if ( string.IsNullOrEmpty( op.NewValue.ToString() ) )
optionSetValue = int.Parse( op.OldValue.ToString() );
else
optionSetValue = int.Parse( op.NewValue.ToString() );
if ( optionSetValue > -1 )
objNewRecord[result.TargetAttributeName.ToLower()] = new OptionSetValue( optionSetValue );
}
}
break;
case "DateTime":
if ( colsValue.PropertyType == typeof( DateTime ) )
{
var itemValue = colsValue.GetValue(Row, null);
if ( colsValue.Name != "CreatedOn" )
objNewRecord[result.TargetAttributeName.ToLower()] = DateTime.Parse( itemValue.ToString() );
else
objNewRecord[CREATED_ON] = DateTime.Parse( itemValue.ToString() );
}
break;
case "TwoOption":
if ( colsValue.PropertyType == typeof( bool ) || colsValue.PropertyType == typeof( Boolean ) )
objNewRecord[result.TargetAttributeName.ToLower()] = colsValue.GetValue( Row, null );
break;
case "Money":
if ( colsValue.PropertyType == typeof( Decimal ) || ( colsValue.PropertyType == typeof( decimal ) ) )
{
Money crmMoney = new Money();
crmMoney.Value = Convert.ToDecimal( colsValue.GetValue( Row, null ) );
objNewRecord[result.TargetAttributeName.ToLower()] = crmMoney;
}
break;
case "WholeNumber":
if ( colsValue.PropertyType == typeof( Decimal )
|| colsValue.PropertyType == typeof( decimal )
|| colsValue.PropertyType == typeof( int )
|| colsValue.PropertyType == typeof( Int32 )
|| colsValue.PropertyType == typeof( float )
|| colsValue.PropertyType == typeof( double )
|| colsValue.PropertyType == typeof( Double )
)
{
if ( colsValue.PropertyType == typeof( Decimal ) || colsValue.PropertyType == typeof( decimal ) )
objNewRecord[result.TargetAttributeName.ToLower()] = Convert.ToDecimal( colsValue.GetValue( Row, null ) );
else if ( colsValue.PropertyType == typeof( int ) || colsValue.PropertyType == typeof( Int32 ) )
objNewRecord[result.TargetAttributeName.ToLower()] = int.Parse( colsValue.GetValue( Row, null ).ToString() );
else
objNewRecord[result.TargetAttributeName.ToLower()] = Convert.ToDouble( colsValue.GetValue( Row, null ) );
}
break;
default:
break;
}
}
}
Each entity will be added to List, then we will create multiple request to D365 in PostExecute
public override void PostExecute ( )
{
base.PostExecute();
int totalPage = 0;
int page = 1;
MigrationUltilities paUltility = new MigrationUltilities();
paUltility.Connection = new SqlConnection( strStagingDbConnectionString );
string strErrorMessage = string.Empty;
#region Init: Init Crm Service for request process
try
{
//Init crm connection via Crm Connection string
CrmConnectionManager connectionManager = new CrmConnectionManager
(
strCrmConnectionString.Decrypt(), true
);
if ( connectionManager.CrmServiceClient.IsReady )
{
this.crmService = connectionManager.Service;
this.crmServiceClient = connectionManager.CrmServiceClient;
}
else
{
LogErrorInException( batchId, Guid.Empty.ToString(), "Cannot init the Crm Service!" );
}
}
catch ( Exception ex )
{
LogErrorInException( batchId, Guid.Empty.ToString(), strCrmConnectionString.Decrypt() );
throw ex;
}
#endregion
#region Process: Processing multiple request to Crm
if ( paUltility.Connection.State == ConnectionState.Closed )
paUltility.Connection.Open();
if ( lstEntities.Count > 0 )
{
DataTable dt = paUltility.funCreateProcessLogTable
(
new EntityCollection(lstEntities)
, strPackageName
, strActionType
, DateTime.Now
, batchId
, objMapping.PrimaryKeyTarget.ToLower()
, false
);
if ( dt.Rows.Count > 0 )
paUltility.funCreateProcessLog( dt, 300000, 500000 );
else
throw new Exception( "Cannot create processing log table." );
paUltility.Connection.Dispose();
totalPage = lstEntities.funGetTotalPage( 300 );
while ( page <= totalPage )
{
var records = lstEntities.funGetPage(page, 300);
EntityCollection recordCollection = new EntityCollection(records);
try
{
this.crmService.funCreateMultipleRecordAsync( recordCollection, requestCallBack );
page++;
}
catch ( Exception ex )
{
LogErrorInException( batchId, Guid.Empty.ToString(), "Error in PostExecute, details: " + ex.Message );
throw ex;
}
}
}
#endregion
}<span data-mce-type="bookmark" id="mce_SELREST_start" data-mce-style="overflow:hidden;line-height:0" style="overflow:hidden;line-height:0" ></span>
There are some methods are not mentioned in this post, it uses my own library, I will upload source and share to you. These article just give to us 1 basic but effective to migrate data from CRM 2011 to D365 quickly and less cost..
Hope that it will be useful for some one.
*This post is locked for comments