SSIS Integration Toolkit for NetSuite

Premium Service Lookup for Netsuite

The NetSuite toolkit offers a Premium Service Lookup component which provides lookup capabilities by searching records in a NetSuite instance. The Premium Service Lookup (also short for "PSL" below) component was first introduced in our v21.1 release, any prior versions will not have the component.

The Premium Service Lookup component is a shared component that is shipped with a few other integration toolkits including SSIS Productivity Pack, SSIS Integration Toolkit for Microsoft Dynamics 365, and SSIS Integration Toolkit for HubSpot, since the v21.1 release.

In order to use the Premium Service Lookup component to look at data from a NetSuite instance, you will need to have the NetSuite Toolkit installed. Once installed, you may continue with the configuration of the component using the following four different pages.

General Page

The General page of the PSL Component allows you to specify the general settings of the component.

General

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.

NetSuite (KingswaySoft) Settings

Use Netsuite Search API

Enable this option to use Netsuite Search API.

Use Advanced Search

NetSuite Advanced search provides users with the ability to perform a search that references an existing saved search or add additional filtering conditions on top of an existing saved search.

Sub Object

Some common source objects have sub objects. Sub objects are objects that are closely related to each other but are not exactly the same. If sub objects are available for a source object one can be selected in the sub object combobox. If a sub object is not selected source objects of all types of sub objects will be returned.

Batch size

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

Saved Search

The Saved Search available to the object can be retrieved here.

Run Scripts and Triggers

The scripts and triggers can be specified to run or not based on the option selected:

• Default: This would pick the settings for running scripts and triggers from the NetSuite side.
• True: This would run the scripts and triggers while the component is executed.
• False: This would not run the script and trigger while the component is executed.

Custom Fields

Clicking the Custom Fields button will launch the 'Manage Custom Field' dialog. This dialog allows additional fields to be added that correspond to custom fields on the object. Fields can be added manually or detected automatically. If the Autodetect option is used, the component will retrieve the specified number of records which will be based on the Page Size and Number of Pages from NetSuite. Then it will populate the grid with any custom fields found.

When the 'Add Manually' button is clicked the 'Add Custom Field' dialog will be launched where you can configure the custom field properties.

Refresh Component Button

Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each field to its most recent metadata.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document that describes the component's metadata including relevant mapping, and so on.

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 a 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 Colum: 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

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