Data Migration Manager: Advanced Transformations
Advanced transformations used in Microsoft CRM Data Migration Manager is a powerful and efficient technique for managing data when migrating your data. In one of my previous posts, Data Migration Manager: Advanced Transformation Mappings, I discussed the basics of advanced transformation mapping and explained how advanced transformation mappings can be added to the data maps used by the Data Migration Manager. In this post I will walk you through all the available advance transformations when migrating your data. I will also show you how the mapping can be done for each of them and will give you some examples of where they will be useful.
The Data Migration Manager provides complete support for eight different advance transformations. These transformations are in no way designed to solve every transformation problem that the user may face but will help him in most cases without making tedious changes to source data files.
Following is the list of advance transformations available for use with the Data Migration Manager:
1. Concatenation
Type Name: Microsoft.Crm.Transformations.Concatenation
Description: Transformation used to concatenate strings.
Pseudo Signature:
string Concatenate(string prefix, string suffix, string delimiter, string[] inputStrings)
Sample Usage:
1: <TransformationMap>
2: <TransformationTypeName>Microsoft.Crm.Transformations.Concatenate</TransformationTypeName>
3: <ProcessCode>Process</ProcessCode>
4: <InputParameterMaps>
5: <SingletonInputParameterMaps>
6: <SingletonInputParameterMap>
7: <ParameterSequence>1</ParameterSequence>
8: <DataTypeCode>Value</DataTypeCode>
9: <Data>MyPrefix</Data>
10: </SingletonInputParameterMap>
11: <SingletonInputParameterMap>
12: <ParameterSequence>2</ParameterSequence>
13: <DataTypeCode>Value</DataTypeCode>
14: <Data>MySuffix</Data>
15: </SingletonInputParameterMap>
16: <SingletonInputParameterMap>
17: <ParameterSequence>3</ParameterSequence>
18: <DataTypeCode>Value</DataTypeCode>
19: <Data>” “</Data>
20: </SingletonInputParameterMap>
21: </SingletonInputParameterMaps>
22: <ArrayInputParameterMaps>
23: <ArrayInputParameterMap>
24: <ParameterSequence>4</ParameterSequence>
25: <Items>
26: <Item>
27: <ParameterArrayIndex>0</ParameterArrayIndex>
28: <DataTypeCode>Reference</DataTypeCode>
29: <Data>FirstName</Data>
30: </Item>
31: <Item>
32: <ParameterArrayIndex>1</ParameterArrayIndex>
33: <DataTypeCode>Reference</DataTypeCode>
34: <Data>LastName</Data>
35: </Item>
36: </Items>
37: </ArrayInputParameterMap>
38: </ArrayInputParameterMaps>
39: </InputParameterMaps>
40: <OutputParameterMaps>
41: <OutputParameterMap>
42: <ParameterSequence>1</ParameterSequence>
43: <Data>FullName</Data>
44: </OutputParameterMap>
45: </OutputParameterMaps>
46: </TransformationMap>
Note: Omitting any parameter mapping will result in sending a null value to that parameter during evaluation.
Use Cases:
Useful in cases where a Microsoft CRM field is concatenated aggregate of more than one fields in the source system (example above). Also useful in cases where a constant needs to be added to a source string. A good example would be when we are trying to map something to kbarticle.articlexml field. In most cases, the article string coming from the source system will have to be enclosed in an xml container.
A small snippet for such a mapping follows:
1: <InputParameterMaps>
2: <SingletonInputParameterMaps>
3: <SingletonInputParameterMap>
4: <ParameterSequence>1</ParameterSequence>
5: <DataTypeCode>Value</DataTypeCode>
6: <Data><articledata><section id='0'><content><![CDATA[</Data>
7: </SingletonInputParameterMap>
8: <SingletonInputParameterMap>
9: <ParameterSequence>2</ParameterSequence>
10: <DataTypeCode>Value</DataTypeCode>
11: <Data>]]></content></section></articledata></Data>
12: </SingletonInputParameterMap>
13: </SingletonInputParameterMaps>
14: <ArrayInputParameterMaps>
15: <ArrayInputParameterMap>
16: <ParameterSequence>4</ParameterSequence>
17: <Items>
18: <Item>
19: <ParameterArrayIndex>0</ParameterArrayIndex>
20: <DataTypeCode>Reference</DataTypeCode>
21: <Data>SolutionNote</Data>
22: </Item>
23: </Items>
24: </ArrayInputParameterMap>
25: </ArrayInputParameterMaps>
26: </InputParameterMaps>
2. Split
Type Name: Microsoft.Crm.Transformations.Split
Description: Transformation used to split a string into multiple strings based on a delimiter.
Pseudo Signature:
string[] Split(string inputString, string delimiter)
Sample Usage:
1: <TransformationMap>
2: <TransformationTypeName>Microsoft.Crm.Transformations.Split</TransformationTypeName>
3: <ProcessCode>Process</ProcessCode>
4: <InputParameterMaps>
5: <SingletonInputParameterMaps>
6: <SingletonInputParameterMap>
7: <ParameterSequence>1</ParameterSequence>
8: <DataTypeCode>Reference</DataTypeCode>
9: <Data>FullName</Data>
10: </SingletonInputParameterMap>
11: <SingletonInputParameterMap>
12: <ParameterSequence>2</ParameterSequence>
13: <DataTypeCode>Value</DataTypeCode>
14: <Data>” “</Data>
15: </SingletonInputParameterMap>
16: </SingletonInputParameterMaps>
17: </InputParameterMaps>
18: <OutputParameterMaps>
19: <OutputParameterMap>
20: <ParameterSequence>1</ParameterSequence>
21: <Data>firstname</Data>
22: </OutputParameterMap>
23: <OutputParameterMap>
24: <ParameterSequence>2</ParameterSequence>
25: <Data>lastname</Data>
26: </OutputParameterMap>
27: </OutputParameterMaps>
28: </TransformationMap>
Use Cases:
Useful in cases where a source field is concatenated aggregate of more than one fields in Microsoft CRM. E.g. when we export data out of Microsoft CRM, some of the fields like contact.fullname is exported which is an aggregate attribute.
3. Substring
Type Name: Microsoft.Crm.Transformations.Substring
Description: Transformation used to obtain a substring of a specified length, starting at a specified point in the string.
Pseudo Signature:
string Substring(string inputString, int startIndex, int length)
Sample Usage:
1: <TransformationMap>
2: <TransformationTypeName>Microsoft.Crm.Transformations.Substring</TransformationTypeName>
3: <ProcessCode>Process</ProcessCode>
4: <InputParameterMaps>
5: <SingletonInputParameterMaps>
6: <SingletonInputParameterMap>
7: <ParameterSequence>1</ParameterSequence>
8: <DataTypeCode>Reference</DataTypeCode>
9: <Data>BigDescription</Data>
10: </SingletonInputParameterMap>
11: <SingletonInputParameterMap>
12: <ParameterSequence>2</ParameterSequence>
13: <DataTypeCode>Value</DataTypeCode>
14: <Data>0</Data>
15: </SingletonInputParameterMap>
16: <SingletonInputParameterMap>
17: <ParameterSequence>3</ParameterSequence>
18: <DataTypeCode>Value</DataTypeCode>
19: <Data>500</Data>
20: </SingletonInputParameterMap>
21: </SingletonInputParameterMaps>
22: </InputParameterMaps>
23: <OutputParameterMaps>
24: <OutputParameterMap>
25: <ParameterSequence>1</ParameterSequence>
26: <Data>description</Data>
27: </OutputParameterMap>
28: </OutputParameterMaps>
29: </TransformationMap>
Use Cases:
Useful in cases where we need to chop off some portion of the input string. E.g. If there is a field in the source system which is very large in size and cannot be accommodated in the corresponding CRM field, then we can decide to take only the starting N characters that can be accommodated.
Note: If the parameter length is not mapped or the length has been mapped to a value greater than the string’s length, the whole string starting from the start index is returned.
4. Replace
Type Name: Microsoft.Crm.Transformations.Replace
Description: Transformation used to replace all occurrences of a specified string with another specified string.
Pseudo Signature:
string Replace(string inputString, string searchString, string replaceString)
Sample Usage:
1: <TransformationMap>
2: <TransformationTypeName>Microsoft.Crm.Transformations.Replace</TransformationTypeName>
3: <ProcessCode>Process</ProcessCode>
4: <InputParameterMaps>
5: <SingletonInputParameterMaps>
6: <SingletonInputParameterMap>
7: <ParameterSequence>1</ParameterSequence>
8: <DataTypeCode>Reference</DataTypeCode>
9: <Data>WhatId</Data>
10: </SingletonInputParameterMap>
11: <SingletonInputParameterMap>
12: <ParameterSequence>2</ParameterSequence>
13: <DataTypeCode>Value</DataTypeCode>
14: <Data>000000000000000AAA</Data>
15: </SingletonInputParameterMap>
16: <SingletonInputParameterMap>
17: <ParameterSequence>3</ParameterSequence>
18: <DataTypeCode>Value</DataTypeCode>
19: <Data></Data>
20: </SingletonInputParameterMap>
21: </SingletonInputParameterMaps>
22: <ArrayInputParameterMaps />
23: </InputParameterMaps>
24: <OutputParameterMaps>
25: <OutputParameterMap>
26: <ParameterSequence>1</ParameterSequence>
27: <Data>regardingobjectid</Data>
28: <LookupMaps>
29: <LookupMap>
30: <LookupEntityName>account</LookupEntityName>
31: <LookupAttributeName>accountid</LookupAttributeName>
32: <LookupType>System</LookupType>
33: <ProcessCode>Process</ProcessCode>
34: </LookupMap>
35: </LookupMaps>
36: </OutputParameterMap>
37: </OutputParameterMaps>
38: </TransformationMap>
Use Cases:
Useful in cases where we need to remove a substring from the input string. Not mapping replaceString achieves such a behavior. E.g. in the mapping shown above, the user wants to remove a static code from the field. Another example would be if the user wants to reflect a change in URL in all her fields.
5. Assign Value
Type Name: Microsoft.Crm.Transformations.AssignValue
Description: Transformation used to replace all values with a specified value.
Pseudo Signature:
string AssignValue(string valueToAssign)
Sample Usage:
1: <TransformationMap>
2: <TransformationTypeName>Microsoft.Crm.Transformations.AssignValue</TransformationTypeName>
3: <ProcessCode>Process</ProcessCode>
4: <InputParameterMaps>
5: <SingletonInputParameterMaps>
6: <SingletonInputParameterMap>
7: <ParameterSequence>1</ParameterSequence>
8: <DataTypeCode>Value</DataTypeCode>
9: <Data>1</Data>
10: </SingletonInputParameterMap>
11: </SingletonInputParameterMaps>
12: <ArrayInputParameterMaps />
13: </InputParameterMaps>
14: <OutputParameterMaps>
15: <OutputParameterMap>
16: <ParameterSequence>1</ParameterSequence>
17: <Data>featuremask</Data>
18: </OutputParameterMap>
19: </OutputParameterMaps>
20: </TransformationMap>
Use Cases:
Useful in cases where we do not have values in the source system corresponding to some required fields in Microsoft CRM. Using this transformation we can add default values to Microsoft CRM fields.
6. Add To Date
Type Name: Microsoft.Crm.Transformations.AddToDate
Description: Transformation used to add a specified number of days, months, and years to a date.
Pseudo Signature:
string AddToDate(string date, int yearOffset, int monthOffset, int dayOffset)
Sample Usage:
1: <TransformationMap>
2: <TransformationTypeName>Microsoft.Crm.Transformations.AddToDate</TransformationTypeName>
3: <ProcessCode>Process</ProcessCode>
4: <InputParameterMaps>
5: <SingletonInputParameterMaps>
6: <SingletonInputParameterMap>
7: <ParameterSequence>1</ParameterSequence>
8: <DataTypeCode>Reference</DataTypeCode>
9: <Data>StartDate</Data>
10: </SingletonInputParameterMap>
11: <SingletonInputParameterMap>
12: <ParameterSequence>3</ParameterSequence>
13: <DataTypeCode>Reference</DataTypeCode>
14: <Data>ContractTerm</Data>
15: </SingletonInputParameterMap>
16: </SingletonInputParameterMaps>
17: <ArrayInputParameterMaps />
18: </InputParameterMaps>
19: <OutputParameterMaps>
20: <OutputParameterMap>
21: <ParameterSequence>1</ParameterSequence>
22: <Data>expireson</Data>
23: </OutputParameterMap>
24: </OutputParameterMaps>
25: </TransformationMap>
Use Cases:
Useful in cases where we need to offset the dates by some interval. For example, as seen above, the source system has a start date and an interval whereas Microsoft CRM has an end date.
There are two more advance transformations related to date/time manipulation which lets the user do extensive manipulation with the current date. You can find the documentation for all the advance transformations here.
The advance transformations can open a lot of new doors while transforming the data during data migration. I encourage you to try these transformations to discover newer and easier ways to migrate disparate data into Microsoft CRM based on the special needs of your implementation.
