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.

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.

On this page, you can easily add any columns that should be returned. Note that at the bottom of the page, you can include two special lookup result fields.

• _MatchFound - this will indicate whether the lookup has succeeded. It should return a true when there is at least a match found in the target system. Otherwise, it should return a false.
• _HasMoreThanOneMatch - this will be set to true when there are more than one matching record in the target system.

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