Usually, mapping data to a lookup field would only take you passing the GUID of the record. However, working with custom activity entity, you may notice some lookup fields are actually of type Party List such as Customers, To and From of the email, etc. On this note, Kingswaysoft is mapping this not as a unique identifier but as ntext.

mapping a party list field using kingswaysoft in ssis

 

look-up-record

 

If you try to create a new Phone Call record and set a value in To field, you should see that you may select records from several entities like Account and Contact.

So how are we going to map this one using Kingswaysoft?

Specifying input values for a CRM activityparty field in a CRM Destination Component takes two types of inputs:

  1. JSON
  2. Delimited Text

I have come across this problem when I was doing my mapping in Phone Call activity. In this blog, I will focus on the delimited text which I used on my mapping. For more information using JSON, you may refer to this site.

Through delimited text, input needs to be constructed using semicolon (;) and colon (:) delimiters.

Above is a sample value that you can set for an activitypartyfield like the To and From of the Phone Call activity. Each lookup consists of two parts, divided by a colon (:), with the first part being the entity name and the second part being the entity record’s GUID. If you want to add another lookup value, append semicolon (;) as a delimiter. This will serve as the separator of values.

For the email entity, you can either use the above format or specify the party list by concatenating them using semicolon (;) as delimiter, like this one:

Note that when using Delimited Text format, there is no way to perform text lookup, you would have to always pass in primary key values in GUID format.

Note that Delimited Text is the only supported format if you are using v4.1 or prior.

Applying it through SSIS, you can use the Derived Column to build your lookup values instead of adding a column and constructing a query. You just have to connect the Derived Column component from your source connection.

derived-column-component

 

Click the Derived Column component and you should see the editor. Create a new derived column to place your newly built expression. On your expression, concatenate the entity string and the GUID. Please take note to cast your GUID to string first and remove the open and close bracket of the GUID.

derived-column-transformation-editor

 

After this, you’re good to go for your mapping!