SSIS Lookups in Microsoft Dynamics 365 Sales
Quite often in a Dynamics 365 CDS/CRM/CE/Dataverse integration project, the need arises to perform a lookup against those records because you do not have the GUID value (the primary key), but rather only some text values with which to search.
There are several different ways to approach this problem, depending on the circumstances. KingswaySoft offers several add-on components for Dynamics 365 that make lookups possible. One example is the
- The in-place lookup will occur as data is being written to a destination component. In this scenario, since there is no transformation component, it can’t be used to transform the data.
- The in-place lookup only supports up to two lookup fields.
In the past, we would recommend our
We noted these challenges and limitations facing developers and saw the need for another tool. So, as part of the 2021 release wave 1 of our
The new component provides lookup capabilities by searching through records in the target system, which supports many database or application-based connections such as
Today, we’ll show you how to use Premium Service Lookup in 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.
In most cases, you would first use an SSIS source component to read data from the source system, then pass the data to Premium Service Lookup.
There are 3 cache modes in Premium Service Lookup that allow you to balance performance with real-time data availability.
- Full Cache: When selected, all records are preloaded from the target object into cache memory before performing the lookup. This is ideal when there are relatively few records in the target object.
- Partial Cache: When chosen, the component will gradually build the lookup cache as the data load progresses. It starts with an empty cache. When an input row enters the component, it uses your predefined lookup conditions to attempt to find a matching record in the target object. If it finds a match, then both the key and the lookup values are added to the local cache. If that same key turns up again, the lookup values are pulled from the local cache instead of the target entity. This option is most performant when working with a large target data set. For example, if you have several hundred thousand records in your lookup entity, and you are only processing a few hundred records for your primary entity, Partial Cache mode would be the fastest.
- No Cache: In this mode, the component will not cache 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 the most accurate when you need values in real time.
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, let’s configure the lookup match conditions in the Lookup Conditions section.
As you can see in the screenshot, ‘firstname’ and ‘lastname’ columns in the source data will be compared with the ‘firstname’ and ‘lastname’ fields of all contacts created within the last 5 days in the target Dynamics 365 CDS/CRM/CE/Dataverse system. Note how this overcomes the constraints of previous solutions:
- The component contains more than 2 lookup conditions
- One of the lookup conditions 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
Next, we select the columns in the target lookup table to add them to the data pipeline on the Output Columns page. In our example, we select ‘contactid’ as our output column.
You may notice two additional output columns that can be used for validating the outputs.
- _MatchFound – This field shows whether a match is found (or not) as a boolean result.
- _HasMoreThanOneMatch – This field shows whether there is more than one match as a boolean result.
The overall data flow design of the package looks like this:
Once the lookup has been performed, we use a Conditional Split component before writing to Dynamics 365 CDS/CRM/CE/Dataverse, allowing us to further limit the results to output records that have only one match in the target system, then redirecting them to a 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.
Now you’ve seen how you can use our Premium Service Lookup component to find Dynamics 365 CDS/CRM/CE/Dataverse records. If you need to perform a lookup with complex conditions, the
Would you like to know more about the other components in our
By KingswaySoft |