Premium Service Lookup for Dynamics 365 CDS/CRM

Premium Service Lookup for Dynamics 365 CDS/CRM

The SSIS Integration Toolkit for Microsoft Dynamics 365 offers a Premium Service Lookup component that provides lookup capabilities by searching records in Dynamics 365 CE/CRM or CDS instances. The Premium Service Lookup component, short for "PSL", was first introduced in our v21.1 release, any prior versions will not have the it.

The Premium Service Lookup component is a shared component that is shipped by a few other integration toolkits, such as SSIS Productivity Pack, SSIS Integration Toolkit for HubSpot, and SSIS Integration Toolkit for NetSuite, since the v21.1 release.

In order to use the Premium Service Lookup component to look at data from a Dynamics 365 CE/CRM or CDS instance, you will need to have the SSIS Integration Toolkit for Microsoft Dynamics 365 installed. Once installed, you may continue with the component's configuration using the following four pages:

• General
• Lookup Conditions
• Output Columns
• Error Handling

General Page

The General page of the Premium Service Lookup Component allows you to specify the general settings of the component.

Connection Manager

The Connection Manager option shows all PSL-compatible connection managers. When working with Dynamics 365 CRM/CDS, you would choose a DynamicCRM or CDS connection manager here in order to perform such lookup.

Target Object

The Target Object option allows you to choose a CRM/CDS entity that you would read data from, for the lookup purpose.

Max Returned Matches(since v24.1)

The Max Returned Matches option allows you to specify the number of matched records to be returned. The default setting of the option is 1, which should be most commonly used option for typical use cases. When the option is set to be greater than 1, the component will change to be an asynchronous component. When the option is changed to 0, it will return all matching records. If it happens to be changed back to 1 again, the component will work as a synchronous component.

Cache Mode

There are 3 Cache Mode options available when looking up records from a Dynamics 365 CRM/CDS instance.

• Full Cache
• Partial Cache
• No Cache

Output Timezone

The Output Timezone option specifies how the datetime values are produced. There are two options available:

• UTC (Default)
• Adjust to timezone of Connection User

Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time during the lookup. This option is mainly designed for the Full Cache option discussed above. The default value is 2000. The maximum allowed value is 5000. For performance reasons, this parameter should not be too small.

Lookup Conditions Page

The Lookup Conditions page allows you to create filtering conditions for the lookup purpose. The page comes with a condition builder on the top which you may use to create matching conditions in order to return the needed records in the system.

The condition builder supports various operators, including the "equal to", "like", "between", among many others. It is important to note that only "equal to" operator is supported by the Full Cache mode discussed above. If you have chosen an operator that is not supported by the Full Cache mode, the Cache Mode option will be automatically updated to the Partial Cache option.

Once you have built such matching conditions, the component should display the query that will be used during runtime. It is important to note that the query displayed may not be the exact query sent to Microsoft Dynamics 365 CRM/CDS. In particular, the input value is generally represented by a special token during the design time in order to support the input column, SSIS variables, etc. Those tokens will be replaced automatically during runtime.

When building such matching conditions, it is worth noting that the Output Timezone option discussed above will have an impact on how the date time input values are used for matching purposes. When the Output Timezone is set to UTC, all datetime values will be converted to UTC based on the connection user's time zone setting in the system before matching.

Output Columns Page

The Output Columns page allows you to add columns from the lookup entity to be returned as output columns for the lookup component.

There are two Additional Output Columns that can be used for validating the outputs.

• _MatchFound – This field returns true if any matching records have been found in the lookup object or system. Or otherwise, it should return a false.
  
• _HasMoreThanMaxReturnedMatches – This field will be true if there are more matching records in the lookup object or system than the specified Max Returned Matches option, or otherwise it should return a false value.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen.

There are three options available that you can choose for the component's error handling:

1. Fail on error
2. Redirect rows to error output
3. Ignore error