Using the NetSuite Source Component
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.
- 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.
There are Two Service endpoints that our Netsuite components support - SOAP and REST. Depending on which is chosen in the connection manager, the configurations would slightly vary. The below links would show the settings and how the component can be configured while working with both SOAP API and REST API.
- 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.
- REST (Since v25.1)
- If you have connected to your NetSuite Instance using the REST Endpoint the Source component the component will appear in this format.
-
- Source Type
-
The Source Type option allows you to specify whether you want to read data from a Netsuite object, or use a snippet of a Netusite SuiteQL. Using the Object option, you have the flexibility of visually picking which fields you want to read from the Netsuite object. When SuiteQL is used, you can leverage the capabilities and flexibility of SuiteQL, such as JOIN, filtering criteria, etc. Your business requirements should influence which option to choose.
- Source Object
-
The Source Object option is only available when the Source Type option has been chosen as Object. When you click the drop-down button of this option, the toolkit will automatically retrieve the list of all available Salesforce objects, so that you can pick one.
-
- Batch Size
-
The Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 500.For performance reasons, this parameter should not be too small. Regardless of the number specified for the Batch Size option, our software will automatically page through the entire data set unless there is a value specified for the Max Rows Returned option as discussed below.
- Max Rows Returned (Only available for Object Source Type)
-
The Max Rows Returned option allows you to limit the number of rows returned.
- When specified, the Netsuite source component will stop paging as soon as the exact number of records has been retrieved.
- When the option is set to 0, it will retrieve all satisfying records by automatically paging through the entire data set.
- Output Timezone
-
The Output Timezone option specifies how Netsuite datetime values are retrieved. There are 3 options available:
- Connection Manager Timezone (Default)
- UTC
- Local System Timezone
-
- Object Query
-
When the above Source Type option has been chosen as SuiteQL, you will be presented with a text editor. You can use this to enter a snippet of a query to retrieve data from your Netsuite instance.
There are several advantages of using SuiteQL. You can use SuiteQL to build complex queries that retrieve specific data from the Netsuite. You are also able to use JOINs in the SuiteQL query so that you read data from multiple entities. You can also apply filtering criteria to only retrieve data that meets the criteria, so you only work with the data you are interested in.
We also support working with SSIS variables in the SuiteQL query. You can use the toolbar to add variables to your query in order to parameterize the query.
- 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.
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.
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 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.