A Performant Solution for SSIS Lookups on Dynamics 365 Data

When it comes to integrating data, especially in complex systems like Dynamics 365, a common task is to search through records using text values instead of direct identifiers like a GUID. Imagine you have a list of names and you need to find their corresponding records in a database. This process, known as a lookup, sounds simple but can get tricky and slow, particularly when the database is large or the search criteria are complex.

Typically, solutions to perform lookups in databases involve direct queries or using built-in functions that search based on text fields. While these methods work, they can be slow and inefficient, especially as the volume of data grows. For Dynamics 365, which is often used to manage vast amounts of sales, customer, and operational data, this can lead to significant performance bottlenecks.

As part of our SSIS Integration Toolkit for Microsoft Dynamics 365, we provide a Text Lookup component that can handle such lookups with ease and a good measure of performance. However, it does have its limitations:

• The in-place lookup will occur only when writing to a destination component. 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.

Since there are many users that need more advanced text lookup capabilities, we developed another, more powerful solution.

SSIS Productivity Pack and the Premium Service Lookup Component

Recognizing the need for more efficient lookup methods, KingswaySoft introduced the Premium Service Lookup component as part of its SSIS Productivity Pack. This tool specifically addresses the performance challenges of traditional lookups by offering a powerful, flexible solution. It’s designed to work seamlessly with Dynamics 365 data, among other sources, providing an essential utility for integration projects.

The Premium Service Lookup component stands out for several reasons:

• Flexible Data Matching: It supports exact and fuzzy matching, allowing for more precise lookups based on the data’s nature.
  • Exact matching is great for when you have precise criteria, while fuzzy matching helps find records that might have minor differences or typos.
• Multiple Cache Modes: With options for Full Cache, Partial Cache, and No Cache, it balances between performance and real-time data accuracy.
  • Full Cache mode is ideal for smaller datasets, loading all records into memory for quick access.
  • Partial Cache grows dynamically, adding records to the cache as they’re found, which is efficient for larger datasets.
  • No Cache queries the database in real-time for each lookup, ensuring the most up-to-date data at the expense of speed.

How to Use the SSIS Premium Service Lookup Component in Dynamics 365

Let’s simplify how to use the Premium Service Lookup component with Dynamics 365 data:

1. Start with a Source Component: Your first step involves using an SSIS source component to read data from your initial system. This prepares your data for lookup.
2. Configure the Premium Service Lookup: Next, you’ll set up the Premium Service Lookup component. This includes choosing your Dynamics 365 connection and selecting the target entity for your lookups, like contacts or sales records.
3. Select Cache Mode: Choose between Full Cache, Partial Cache, or No Cache based on your dataset size and need for real-time data.
4. Define Lookup Conditions: Specify the conditions under which matches should be found. For example, you might match first and last names from your source data to those in Dynamics 365 records created in the last 5 days.
5. Select Output Columns: Decide which columns from the matched records should be added to your data pipeline. This could include identifiers like contactid to use in further processing.
6. Handle the Results: Finally, use components like Conditional Split to manage the lookup results, directing matched records to their next destination in your data flow.

A Suite of Tools for Dynamics 365 / SSIS Integration Projects

The Premium Service Lookup Component exemplifies the powerful tools available within our SSIS Productivity Pack, designed to enhance and streamline Dynamics 365 integration projects. With its advanced lookup features and performance-optimizing options, it represents a significant leap forward in handling data integration tasks effectively.

Premium Service Lookup is just one of many tools available to address a wide range of data integration challenges, offering both efficiency and flexibility. For those working with complex Dynamics 365 data integration, exploring the full capabilities of the SSIS Productivity Pack would be very beneficial.

To discover more about how the SSIS Productivity Pack can support your Dynamics 365 integration projects, or to delve into the specifics of tools like the Premium Service Lookup Component, reaching out to KingswaySoft for additional information is a great next step.

Our expertise and innovative solutions can provide the support needed to tackle even the most challenging integration scenarios. Contact us today!

By KingswaySoft | www.kingswaysoft.com

The post A Performant Solution for SSIS Lookups on Dynamics 365 Data appeared first on CRM Software Blog | Dynamics 365.