Add SSIS Components to Business Intelligence Development Studio's Toolbox

SSIS Integration Toolkit for NetSuite includes two data flow components. They need to be added to the SSIS toolbox before you can use them in an SSIS data flow task.

Note: If you are using SQL Server 2012 or later development environment, you should not need to do this, as SQL Server 2012 or later automatically lists all available pipeline components by scanning the system.

To add the data flow components, create a new data flow task if you do not have one yet and switch to the SSIS data flow page. Right-click on the toolbox area to bring up the context menu, where you can select the "Choose Items..." option as shown below.

Choose items

You will be presented with a window called "Choose Toolbox Items". Switch to the "SSIS Data Flow Items" tab, and select NetSuite Destination and NetSuite Source components from the list.

add NetSuite ssis components

Both data flow components should now appear in your SSIS Toolbox, where you can drag and drop any of them to the design surface of your SSIS data flow task.


Using the NetSuite Source Componen

The NetSuite Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from NetSuite.

The component includes the following two pages of configuration:

  • General
  • Columns

General Page

The General page allows you to configure various options that will help you retrieve the desired data from NetSuite.

NetSuite Source

Connection Manager

The source 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.

Get Deleted Records

Enabling this option will retrieve all deleted records for the selected object from the Source Object drop-down menu. Returned Information includes deletedDate, externalID, internalID, and the name of the record.

Source Object

The source object drop-down lists all available searchable objects. There are two types of objects: Common and Custom.

  • Common Objects: Common objects are any source that does not begin with the word "Custom." Common objects can have lists of child objects which will appear in the child objects dropdown. Search criteria can be specified for common objects in the search area.
  • Custom Objects: Custom objects can be identified as objects that start with the word "Custom." Custom objects are objects that are not built into NetSuite, but were created by a user. Information about the readable columns in a custom object is not built in to the NetSuite source component and will be retrieved from the server when a custom object is selected. Searching for custom objects is available by choosing the name of a search that is created in the NetSuite portal.
Advanced

For all common objects, an advanced version of the object exists. This can be accessed by checking the 'Advanced' checkbox. Advanced objects have similar columns to the common version of the object but are not identical. Advanced objects do not have child objects. When reading from an advanced object only the specified columns are returned, which can make responses come much faster. Searching for advanced objects is available by choosing the name of a search that is created in the NetSuite portal.

Override Search Columns (since v4.0)

When this option is unchecked, the source component would retrieve the available columns based on the definition of the selected NetSuite Saved Search. When this option is checked, the source component allows you to define your own return fields based on the saved search criteria. You will get values on fields that have been checked on the Columns page of the component.

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.

Child Object

Some common source objects have child objects. These are properties that are lists of complex objects, and are best displayed in a separate output. If a source object has child objects they will appear as objects in the child objects combo box, and you can select any you wish to output. Each child object that is selected will create a new output for the SSIS component.

Batch Size

Source objects support batch size which is the number of records that will be returned per web service call. A default of 50 records per web service result is used, but you can set this to any number that your NetSuite server allows up to 1000.

Retrieve First Page Only

The Retrieve First Page Only option can be used to limit the number of records to return when reading from NetSuite. When the option is enabled, the number specified in the Batch Size would be used as the limit parameter.

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.
Search

If you do not wish to retrieve all records for an object there are two ways to search for data:

Field Search: Common and advanced objects each have several searchable fields that can be used to filter results. The fields can each be used once, and all of the conditions must be true for a record to be returned.

Saved Search: Custom and Advanced objects can use saved searches to filter results. Saved searches are created in the NetSuite portal and can contain more advanced logic than the searches used with common objects. When an advanced or custom source object is selected, the list of saved searches will be populated with the names of any saved searches applicable to that object found on the server. For custom objects, all searches for custom objects will be displayed, so it is important to select a saved search that applies to that custom object.

NOTE: When using Saved Search without enabling the Advanced option, the component will return full records based on the saved search criteria.

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 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 shows you all available attributes from the object that you specified on the General page. You may indicate which attributes to include in your source component by checking or unchecking the checkbox next to each attribute.

NetSuite Source - Columns

If a data type field is grey and looks like a button, clicking on it will start cycling through common data types for that field.

Custom Fields Button

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

add NetSuite custom field

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.

add NetSuite custom field

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 Source Component

When a child is selected in the "Child Object" drop-down list in the "General" page, it will be available in the 'Configure the output columns for' drop-down on the columns page. This allows for selecting output for both parent and child fields. All child outputs will have a field called Parent.Id, where Parent is the name of the parent object. This field will be populated with the internal ID of the parent which will allow rows in the child output to be linked back to their parent row.