KingswaySoft provide us a Dynamics CRM OptionSet Mapping component under Common tab which lies inside SSIS toolbox. This component ease the data migration activity.
In my case, I haven’t used the Dynamics CRM OptionSet Mapping component. Reason the optionset mapping component didn’t populated the attributes which were of int type under Input Column field.
Therefore we need to used Data Conversion and Derived Column components.
Assuming, Role is the name of option set attribute. So, under Data Conversion transformation editor we need to change the data type from int to string.
Select the Role attribute under Input Column, system will auto populate Copy of attribute name. In our case it will be “Copy of Role”, select the data type as Unicode string[DT_WSTR] and provide the length of the field as per your requirement. This Data Conversion transformation editor can be used for any attribute data type conversion.
Now, coming to Derived Column component. Here we need to create an expression for mapping the optionset label and value.
Expression used for OptionSet Mapping, if any of the option value is not found while data transmission then the system would enter “Not Found”.
Role == 954660008 ? "Participating Person" : Role == 954660004 ? "Overlay Sales" : Role == 954660000 ? "Account Manager" : Role == 954660001 ? "Sales Reporting Manager" : Role == 954660002 ? "Vertical Head" : Role == 954660003 ? "Geo Head" : Role == 954660005 ? "Co-Owners" : Role == 954660006 ? "Service Offering Lead" : Role == 954660007 ? "Horizontal Head" : "Not Found"
There are multiple expression under Derived Column component which are very use full and can be used as needed such as Mathematical Function(like LOG, ROUND, SQUARE etc), String Function (like TRIM, LTRIM, RTRIM, REVERSE, REPLACE, UPPER, LOWER etc), Date/Time Functions (like DAY, MONTH, YEAR, DATEDIFF etc), NULL Functions(like REPLACE NULL, etc), Type Casts, Operators(like ADD, SUBTRACT, MULTIPLY, DIVIDE etc.)
Hope this post helps you!
Building Solutions - Avinash Verma 