It is a fairly common requirement in a Dynamics 365 CDS/CRM/CE/Dataverse integration project that you may need to perform a lookup of Dynamics 365 CDS/CRM/CE/Dataverse records since the input source data may not have a lookup field's GUID value, but only some text label values.
There are different ways available to perform lookup towards the D365 CDS/CRM/CE/Dataverse instance. Within the KingswaySoft product family, we offer various flexible and easy to use solutions to make this possible. In particular, our dedicated Dynamics components SSIS Integration Toolkit for Microsoft Dynamics 365 comes included with the Text Lookup feature which allows you to perform lookup based on the text values of the target entity, it offers Full Cache and Partial Cache modes along with many fine-tuned features to achieve complex lookup scenarios. However, there are a couple of main constraints when using the in-place lookup feature.
- The in-place lookup is designed to happen as writing occurs in a destination component, there is no corresponding transformation component available, it can't be used as a transformation feature.
- The in-place lookup has a limitation that it can support up to two lookup fields.
While the CDS/CRM destination component's Text Lookup feature is extremely flexible, powerful and easy to use, the above two restrictions are called out on a regular basis in our support cases. We have client inquiries on performing lookup as a transformation feature, or in other cases clients would like to perform lookup for more than 2 columns. Prior to our 2021 release wave 1, we used to recommend using our Premium Lookup component offered in SSIS Productivity Pack - the Premium Lookup component works by taking two inputs, a source data input and a lookup table input, to perform in-memory lookup based on the defined conditions, it offers a flexible way to perform lookup against any data sources and supports both exact match and fuzzy match along with many other matching options. The caveat is the component generally requires a full extract of the lookup table, and it may not perform well when lookup table contains a very significant number of records. We recognized the challenges faced by developers and we saw the need for a different lookup tool, hence we introduced the Premium Service Lookup component in our recent 2021 release wave 1 within our SSIS Productivity Pack product offering. The new component reveal a number of advanced lookup scenarios which were not possible previously. Let's have a quick review of the component first.
The Premium Service Lookup component provides lookup capabilities by searching record in the target system, which supports a number of database or application based connections such as Dynamics 365 CE/CRM or CDS, HubSpot, NetSuite, ADO connection and all of our REST connections. If you want to learn more about the component, you may click here for the detailed information of the component.
In this blog post, we will show you how to use Premium Service Lookup to achieve some advanced lookup scenarios when working with Dynamics 365 CDS/CRM/CE/Dataverse connections.
First, let’s have a quick look at the Premium Service Lookup component.
You would typically connect the Premium Service Lookup component after an SSIS source component which reads data from the source system. You will notice that the component comes with three cache modes, which are Full Cache, Partial Cache and No Cache.
- Full Cache mode: When Full Cache mode is selected, the component will preload all records from the target object into the cache memory, then the lookup will be performed will be performed a lookup from the memory. This is the preferred option when the number of records in target object is small.
- Partial Cache mode: When chosen, the component will gradually build up lookup cache as the data load progresses. It starts with an empty cache, no data is preloaded into the cache memory. When an input row enters the component, it uses the specified lookup conditions to attempt to find a matching record in the target object using the specified query. If a match is found, then both the key and the lookup values are added to the local cache. If that same key enters the component again, it can retrieve the lookup values from the local cache instead of the target entity. This is the preferred option when the number of records in target object is significantly large. For instance, if you have more than a few hundreds of thousands of records in lookup entity, and you are only processing a few hundreds of records for your primary entity, Partial Cache mode would provide better performance.
- No Cache mode: In No Cache mode, the component will not use the cache to store the lookup table at any stage. When a new row comes from the data flow, it will directly query the target object for matching values. This mode is usually the slowest, but it’s useful if a real-time lookup value is required.
In Premium Service Lookup Editor, we specify the Dynamics CRM connection manager and contact entity as our target lookup table. Partial Cache is used in this example.
Next, we will configure lookup match conditions in the Lookup Conditions page.
As shown in the above screenshot, firstname and lastname columns in the source data will check against the last 5 days new contact records’ firstname and lastname in the target Dynamics 365 CDS/CRM/CE/Dataverse system. The above configuration addresses the following three constraints that were not possible when using the in-place lookup feature.
- The component contains more than 2 lookup conditions
- One of the lookup condition is using a special FetchXML function (which translates to a special SQL function when processed on the server side)
- The component is used as a transformation feature within the data flow
We can select the columns in the target lookup table that we need to add to the data pipeline in the Output Columns page. In our example, we select contactid as our output column.
You may notice there are two Additional Output Columns that can be used for validating the outputs.
- _MatchFound – This field shows whether match is found or not as boolean result.
- _HasMoreThanOneMatch – This field shows whether there is more than one match as boolean result.
The overall data flow design of the package is shown in the screenshot below:
Once the lookup has been performed, we use a Conditional Split component before writing to Dynamics 365 CDS/CRM/CE/Dataverse, which uses the following expression to select those output records that has one and only match in the target system to redirect to new destination component down the pipeline.
[LookupResult._MatchFound] == TRUE && [LookupResult._HasMoreThanOneMatch] == FALSE
Lastly, in the final CDS/CRM destination component, we can update the Dynamics 365 CDS/CRM/CE/Dataverse records using the LookupResult.contactid field as shown in the screenshot below.
Closing Notes
In this blog, we demonstrated how you can use our Premium Service Lookup component to find Dynamics 365 CDS/CRM/CE/Dataverse records. If you need to perform lookup with complex conditions the Premium Service Lookup can be a great tool to consider. Depending on your business requirement, other solutions include using Text Lookup or Premium Lookup as described in our earlier blog post are also applicable.