Premium Service Lookup for Dynamics BC/NAV
The SSIS Integration Toolkit for Microsoft Dynamics 365 offers a Premium Service Lookup Component which provides lookup capabilities by searching records in Dynamics BC/NAV instances. The Dynamics BC/NAV connection support in Premium Service Lookup (short for "PSL") was added in our v21.2 release.
The Premium Service Lookup Component is a shared component that is shipped with some other integration toolkits, such as SSIS Productivity Pack, SSIS Integration Toolkit for HubSpot, and SSIS Integration Toolkit for NetSuite toolkit, since the v21.1 release.
In order to use the component to look for data from a Dynamics BC/NAV instance, you will need to have the Dynamics 365 Toolkit installed. Once installed, you may continue with the configuration of the Premium Service Lookup Component using the following four different 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 BC/NAV instances, you would choose a Dynamics BC/NAV manager here in order to perform such lookup.
- Target Object
-
The Target Object option allows you to choose a BC/NAV 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 BC/NAV instance.
- Full Cache
- Partial Cache
- No Cache
- Advanced Text Matching Options
-
In addition to the above options, the Text Lookup feature also offers the following advanced options.
- Ignore case: When chosen, the lookup will perform a case-insensitive lookup.
- Ignore Leading Whitespace: When chosen, the lookup would ignore leading whitespaces.
- Ignore Trailing Whitespace: When chosen, the lookup would ignore any trailing whitespaces.
- Ignore Symbols: When chosen, the lookup would ignore any symbols.
- Ignore Diacritics/Accents: When chosen, the lookup will not take any diacritics within a string into consideration.
- 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 "equals", "greater than", "less than", among many others. 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 run-time. It is important to note that the query displayed may not be the exact query sent to the Microsoft Dynamics BC/NAV instance. In particular, the input value is generally represented by a special token during the design time in order to support input column, SSIS variables, etc. Those tokens will be replaced automatically during run-time.
The Query Editor supports adding filter conditions.
- Add condition (+): Adds a condition to be used in a query.
- Remove condition (-): Removes the condition that was added.
- Arrows: Use arrows to group the lookup conditions.
- AND: Specify AND to create logical expressions of your lookup conditions.
- Lookup Column: Select the lookup column from the drop-down list which displays available columns for the specified Lookup Table.
- Operator: Use the query operator to specify how each input value in a clause must relate to the corresponding value in a lookup table.
- Input Value: The Input Value for the lookup condition. Available options are:
-
Input Column: Enables user to perform Lookup match by selecting a field from the Lookup Table Column
-
Variable: Enables user to perform Lookup match based on a System or User SSIS variable
-
Static Value: Enables user to perform Lookup match based on a static value
You could also switch to use the Custom Query Mode.
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:
- Fail on error
- Redirect rows to error output
- Ignore error