SSIS Integration Toolkit for NetSuite

Using the NetSuite Destination Component

The NetSuite Destination Component is an SSIS data flow pipeline component that can be used to write data to NetSuite. You can create, update, upsert, or delete objects with this component. Inputs to the NetSuite Destination component must be sorted. There are three pages of configuration:

• General
• Columns
• Error Handling

Note: If you supply multiple inputs (child objects) you will need to sort all of the inputs using the SSIS Sort Component. For the primary input sort by the _linkId field and for secondary inputs sort by the _parent.linkId field.

General Page

The General page on the HubSpot Connection Manager allows you to specify general settings for the connection.

Connection Manager

The destination component requires an active web service connection to NetSuite. The Connection Manager drop-down will show a list of all NetSuite Connection Managers that have been created in the current SSIS package.

Destination Object

The destination object field displays a list of available destination objects that allow writing actions.

Action

The Action option allows you to specify how data should be written to NetSuite. There are four write actions supported for each endpoint.

• Create: Create new object(s).
• Update: Update existing object(s) by InternalId.
• Upsert: Search for an object by ExternalId, update the object if found, and create a new object if it doesn't exist.
• Delete: Delete object(s).
  

Replace All Child List Items

If this option is selected, lists of child items will be replaced with new items for update and upsert actions. If the option is not selected lists of child items will append to/update the existing list of child items.

Batch Size

The number of items to attempt to process per request.

Input Timezone

This property tells the component what timezone incoming dates are in. The options are UTC, Local System Timezone, and Connection Manager Timezone.

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.

Child Object Settings

Some common destination objects have child objects. If a destination object has child objects they will appear as objects in the child objects grid and can be mapped to any available inputs.

Refresh Component

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

Map Unmapped Fields

By clicking this button, the component will attempt to map all unmapped columns with input columns of the same name.

Clear All Mappings Button

By clicking this button, the component will reset all your mappings in the destination component.

Expression fx Icon

Click the blue fx icon to launch the 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.

Columns Page

The Columns page allows you to map the columns from upstream components to destination attributes.

On the Columns page, you will see a grid as shown below.

• Input Column: You can select an input column from an upstream component here.
• NetSuite Field: The attribute/field that you want to write data to.
• Data Type: This column indicates the type of value for the current attribute.
• Unmap: This button provides a convenient way to unset the mapping for the selected attribute/field.

Manage Fields Button

When a common object is selected the Add button will be visible on the columns page. Clicking the Manage 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.

For both manual and autodetect columns, the column name can be set to anything, but the Script Id must be the same as the Script Id of the field in NetSuite.

Child Objects in the Destination Component

When a child is mapped in the "Child Object" grid in the "General" page, it will be available in the 'Select Input:' dropdown on the columns page. This allows for selecting input for both parent and child fields. The parent input will have a field called _linkId which can be any string and is used to link the parent to its children. All child outputs will have a field called Parent.Id, where Parent is the name of the parent object. This field should have a value that matches the value used for the linkId of the parent.

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 to write to NetSuite will be redirected to the 'NetSuite Error Output' of the Destination Component. As indicated in the screenshot below, the green output connection represents rows that were successfully written, and the red 'Error Output' connection represents erroneous rows. The 'NetSuiteErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by NetSuite or the component itself.

NOTE: Use extra caution when selecting Ignore error option, since the component will remain silent for any errors that have occurred.

On the Error Handling page, there is also an option that can be used to enable or disable the following output fields for the destination component.

• NetSuiteRecordId: Contains the newly created NetSuite record's Id field value.