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 feature of our . It lets you perform a lookup based on text fields that you can specify. It also allows you to fine-tune performance with Full Cache and Partial Cache modes and other features, letting you tackle complex lookup scenarios. However, there are two principal limitations to this method:
- 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.
The component for Dynamics 365 Sales is powerful and very flexible, meeting the needs of many projects. However, the aforementioned restrictions exclude it as an option in certain scenarios, and those restrictions come up frequently in our support cases. Some customers wish to use a lookup to transform data, or would like to narrow their search further with more lookup fields.
In the past, we would recommend our component offered in the . It takes a source data input and a lookup table input and then will perform the lookup in memory according to the conditions you set. It’s an even more flexible solution than Text Lookup, works with any data source, and supports exact and fuzzy matching, along with other options. There’s a catch, though – performance. It requires a full extract of the lookup table. If that table has a large number of records, performance degrades.
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 , we proudly introduced the component. It opens the door to many new lookup scenarios that were previously not possible.
The new component provides lookup capabilities by searching through records in the target system, which supports many database or application-based connections such as , , , ADO connections, and all of our connections. To learn more, click to read the documentation for Premium Service Lookup, where its features are explained in greater detail.
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.
Next Steps
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 , , or components can help you accomplish almost any lookup project you can conceive.
Would you like to know more about the other components in our and the ? Contact us today, we’d be glad to help.
By KingswaySoft |
