Search code examples
ssisdynamics-crmdata-migrationdataversekingswaysoft

Performing Text Lookup with GUID as an input in Kingswaysoft Dynamics 365 Toolkit

In Kingswaysoft's Dynamics 365 Integration Toolkit, is there any way to lookup/match CRM ownerid GUID from source table to a custom column in destination's user entity?

My problem

For some reason, I have to perform a text lookup from source entity's ownerid field to destination user entity's new_legacyuserid custom column that holds user GUIDs from the source system. But it seems like the Text Lookup Editor doesn't match source ownerid with new_legacyuserid column in the destination user entity, but matches if I pass owneridname column as an input.

I have pre populated the new_legacyuserid with a single GUID from the source as a fallback user indication and this column is null for all other records. So all the records' ownerid should fallback to the default user.

Now, when I pass ownerid as an input to the Text Lookup (please see the image below) the package fails with the following error:

{"error":{"code":"0x80040217","message":"systemuser With Id = be33cd29-671b-e511-80ce-005056ae320c Does Not Exist"}}

However, to test differently, when I pass owneridname as an input to the TextLookup, the package runs successfully and all the two records ownership falls back to the default destination user.

I want to perform the same match using ownerid and not owneridname.

enter image description here

Solution

  • I didn't find any way to use user GUID (OwnerId in this case) in the Text Lookup Editor. It seems that when GUID is passed as an input, the KWS adapter doesn't look at the Text Lookup Editor at all.

    One of my colleague tried this workaround and this worked. You can try that if your source CRM is on-premises.

    • Create a derived column in SQL select statement (OwnerIdString) to store GUID as a string
    • Use OwnerIdString column as input

    Example:

    SELECT task.*, convert(VARCHAR(36), ownerid) AS OwnerIdString FROM task

    This will force the Kingswaysoft adapter to do a Text Lookup based on GUID.