Help Manual - Premium Service Lookup Component

Using the Premium Service Lookup Component

The Premium Service Lookup Component is an SSIS data flow pipeline component that can be used to search for rows in a lookup object based on fields from rows in a primary input. It offers a way to perform lookup against data sources by connecting to them from within the component. The component supports the services shown below as lookup connections:

• Dynamics 365 CDS/CRM
• Dynamics 365 BC/NAV
• Hubspot
• Netsuite
• ADO.NET connections
• OData connections in Productivity Pack(since v21.2)
• REST connections in Productivity Pack

We would be considering the REST service lookup in this Help Manual. For other supported services from the above list, please refer to its respective Online Help Manual pages (Links above). The component has only one input, and the input records are compared to the target lookup object that the Premium Service Lookup component is connected to, based on the specified filters, and provides the output.

The component includes the following four pages to configure how you want to search for data:

• General
• Lookup Conditions
• Output Columns
• Error Handling

General Page

The General page can be used to configure the general configuration settings for the Premium Service Lookup component.

Connection Manager

A Connection Manager is required for the Premium Service Lookup component to connect to. A list of available connection managers will populate the drop-down.

Target Object

The Target Object can be selected from the drop-down list. This would be based on the Connection manager. The target object would be used as the lookup table for the input data.

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

You can select a cache mode from the below options.

• • Full Cache: When chosen, the component will populate a full cache of all records from the target object. This is the preferred option when the number of records in the target object is small.
  • Partial Cache: When chosen, the component will gradually build up lookup cache as the data load progresses. This is the preferred option when the number of records in the target object is significantly large.
  • No cache: No cache is built when this is chosen.

Note: The cache mode would be available based on the Target object chosen. Some objects and queries do not support one or more cache modes, which would be reflected in the component.

Advanced Text Matching Options

The Advanced Text Matching options are as follows

• Ignore case: When chosen, the Lookup will perform a case-insensitive lookup. For instance, "ABC Company" will be treated the same as "abc company".
• Ignore Leading Whitespace: When chosen, the leading whitespaces will be ignored.
• Ignore Symbols: When chosen, any symbols would be ignored.
• Ignore Trailing whitespaces: When enabled, any trailing whitespaces would be ignored.
• Ignore Diacritics/Accents: When chosen, the Lookup will not take any diacritics within a string into consideration.

Note: The Advanced Text Matching Options only apply to Full Cache mode.

Lookup against REST services

Depending on the REST service chosen, the objects and endpoints available would be different. There would be a few additional options available as well, specific to the Rest Service chosen.

REST Settings

Use REST Query Language

Enable this option to use the search/query endpoint for performing the lookup.

Note: Use REST Query Language is supported in the following REST services:

• Freshdesk
• Magento (since v21.2)

Endpoint

The Endpoint drop-down lists all available rest endpoints for the selected object.

Batch Size

Specify the Batch Size which is the number of records that will be returned per web service call.

Output Time Zone

The Output Timezone option lets you specify how all datetime fields should be retrieved. Available options are:

• UTC
• Local

Filter Parameters

You can specify Filtering Parameters so that the component only returns those records for lookup that satisfy such filtering parameters.

Note: Filter fields may vary from object to object depending on endpoints.

Note: This Filter Parameters grid is only available for Full Cache mode.

Lookup Conditions Page

The Lookup Conditions page can be used to configure the lookup settings for the Premium Service Lookup component.

Condition builder Mode

The Condition builder mode provides a grid for creating a query that can be used as the lookup.

The Query Editor supports adding filter conditions.

• Add condition (+): Adds a condition to be used in the query.
• Remove condition (-): Removes the condition that was added.
• Arrows: Use arrows to group the lookup conditions.
• AND/OR: Specify AND or OR 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

Custom Query Mode

Switching the query designer to Custom Query Mode will allow you to freely edit the query text. This can help in some cases where certain advanced queries may not be supported in the condition builder.

Output Columns Page

The Output Columns page can be used to configure the output columns for the Premium Service Lookup component.

Add All Columns

The Add All Columns button when clicked would populate all the columns available in the metadata from the lookup object/endpoint as the output.

Remove All Columns

The Remove All Column button when clicked would remove all columns from the Output columns page.

Enable Default Value

By checking Enable Default Value option, an additional Default value field is added in the columns grid where a default value can be specified. By default this is NULL.

The Columns Grid consists of the below fields.

• <ServiceName> Field: This would show the actual output field from the lookup service selected.
• Output Alias: This field shows the Output Alias name, which is editable
• Derived Column: This field can be used to either add the field as a new column or replace an existing one from the input.
• Default Value: Provide a default value for the field.
• Data Type: The datatype of the field. Based on the field and the service, this field can be clicked to toggle between data types, if allowed.

Additional Output Columns

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.

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

When the Redirect rows to error output option is selected, rows that failed will be redirected to the 'Error Output' output of the Component. As indicated in the screenshot below, the blue output connection represents the rows that were successfully written, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by the service or the component itself.