Generating StarfishETL Cross-Reference Files from MSCRM Picklists

When using Starfish to migrate from Microsoft Dynamics CRM to a different CRM system, you may need to create cross reference files that translate picklist codes to actual text values. This is particularly true if you don’t have a full Dynamics CRM instance setup for the database you are migrating from. In such as situation, the database returns numeric codes for picklist values instead of their actual text. This makes it hard to work with because of the need to manually translate each number to its matching text value.

To solve this problem, use the following SQL query to list the picklist codes and text descriptions from the Dynamics CRM database:

SELECT a.AttributeValue AS PicklistCode
,a.Value AS PicklistText
,cast(a.AttributeValue AS VARCHAR(32)) + '|' + a.Value AS XRefValue
FROM StringMap a
INNER JOIN EntityView b ON a.ObjectTypeCode = b.ObjectTypeCode
WHERE a.AttributeName = ''
AND PhysicalName = '
'

Let's say we need to get the picklist values for the statuscode field in the Incident table. Here is what the query would look like:

SELECT a.AttributeValue AS PicklistCode
,a.Value AS PicklistText
,cast(a.AttributeValue AS VARCHAR(32)) + '|' + a.Value AS XRefValue
FROM StringMap a
INNER JOIN EntityView b ON a.ObjectTypeCode = b.ObjectTypeCode
WHERE a.AttributeName = 'statuscode'
AND PhysicalName = 'incident'

And here is what the results look like:

Notice how the results include the PicklistCode, PicklistText and XRefValue. The XRefValue field contents can be simply copied and pasted into a StafishETL XREF file. Include this XREF file in your StarfishETL map and you should be ready to start mapping!

What is Starfish ETL?

The Starfish ETL (Extract, Transform, Load) suite is a fast, flexible and powerful import/export tool. A simple to configure tool to migrate your data, completely!

Want to try it first?