Installation 

To install and run SSIS Integration Toolkit for NetSuite, your system must have the following components installed. 

  • A supported SSIS design-time or run-time, which can be one of the following:
    • SSIS 2017
    • SSIS 2016
    • SSIS 2014
    • SSIS 2012
    • SSIS 2008 R2
    • SSIS 2008
    • SSIS 2005

    For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).

    Integration Services Runtime Selection

    Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is more preferred) in order to run our software during runtime.

    For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).

    • When targeting SSIS 2017 or 2016, you would use SSDT for Visual Studio 2015 available for download at  https://msdn.microsoft.com/mt186501.aspx
      • Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
    • When targeting SSIS 2014, you can use either one of the following:
    • When targeting SSIS 2012, you can use any one of the following:
    • When targeting SSIS 2008 R2 or earlier, you would have to install the Business Intelligence Development Studio that is shipped with the installation media of the corresponding SQL Server version. 
  • .NET Framework 3.5 
    • .NET Framework 3.5 is only required when you use SSIS 2005.
      • If you are using Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP operating systems, you would install .NET framework by downloading it from Microsoft website.
      • If you are using Windows Server 2008 R2 or 2012 family of operating systems, .NET framework 3.5 should be installed using Server Manager program by adding .NET Framework 3.5.1 features.
      • If you are using Windows 8, go to Control Panel -> Programs and Features -> Turn Windows features on or off, then select .NET Framework (includes .NET 2.0 and 3.0).
  • Windows Installer 4.5 
    • If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
    • If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.

When you have confirmed that your system satisfies the above prerequisites, you can navigate to the KingswaySoft website at http://www.kingswaysoft.com to download the installation package. On the download page you will find two download links. One for x86 systems and one for x64 systems. Make sure to choose and download the correct package for your system. 

download options

Once you click the download link, save the zip file which contains the .msi file. Double click the .msi file to start the setup. A security warning may appear. You can safely accept the warning and continue the setup.

The Setup Wizard will appear. Click ‘Next’ to continue.

NetSuite data integration setup

Select the checkbox to agree to the terms of the End-User License Agreement. Click 'Next' to continue.

NetSuite data integration setup EULA

Next, select one of the three setup types that best suits your needs.

NetSuite data integration choose setup

Click ‘Install’ to begin the installation.

NetSuite data integration setup install

To complete the installation, select 'Finish'.

NetSuite data integration setup complete

Setup a NetSuite Connection Manager

The NetSuite Connection Manager is an SSIS connection manager component that can be used to establish connections with NetSuite.

To add a new connection, right-click the Connection Manager area in your Visual Studio project, and choose "New Connection..." from the context menu. You will be prompted the "Add SSIS Connection Manager" window. Select the "NetSuite (KingswaySoft)" item to add a new NetSuite Connection Manager.

new connection

add NetSuite SSIS connection

The NetSuite Connection Manager contains the following 3 pages of configuration. 

  • General
  • Advanced Settings
  • More Info  

General page

The General page allows you to specify connection properties and login credentials to NetSuite. 

NetSuite SSIS Connection Manager

Server URL

The Server URL field lets you specify the URL of the NetSuite instance that you will connect to.

Authentication Type
  • Request - Each request sent to NetSuite contains authentication credentials.
  • Session - The component creates a session and uses it to send requests to NetSuite so credentials are not sent with every request.
Email

The email address of your NetSuite account.

Password

The password of your NetSuite Account

Account ID

The ID associated with your account.  To find your account ID follow these instructions:

  1. Login to the NetSuite portal located here: www.netsuite.com/portal/home.shtml
  2. Navigate to Setup -> Integration -> Web Services preferences
  3. Locate the field called ACCOUNT ID
Application ID

The application ID that the KingswaySoft NetSuite components will use to interact with your NetSuite instance. If you don't have an application ID you can create one by following these instructions:

  1. Login to the NetSuite portal located here: www.netsuite.com/portal/home.shtml
  2. Setup -> Integration -> Manage Integrations -> New
  3. Enter a name such as "KingswaySoft"
  4. Click the 'Save' button
  5. On the confirmation page you should see that an application ID was generated.
User Timezone

The timezone that is specified in the preferences of the user account. It is important that this property is set correctly so that date values read from the server can be interpreted correctly.

Role ID

The ID of the role you wish to use.  Leave this field blank to use the default role.

Max Concurrent Requests

NetSuite accounts typically have a limit on the number of concurrent requests and it will return an error if too many concurrent requests are sent. The component can limit the number of concurrent requests that will be sent to prevent errors.

Timeout

The timeout property will determine when long running server requests are considered to have timed out. After a request has timed out an error will be raised.

Test Connection 

After all the connection information has been provided, you may click the "Test Connection" button to test if the user credentials entered can authenticate with NetSuite.

Advanced Settings

The Advanced Settings page allows you to configure your NetSuite connection to Retry on Intermittent Error and to use a proxy server. 

NetSuite ssis connection manager advanced settings

Retry On Intermittent Errors

This is an option designed to help recover from possible intermittent outages or disruption of service. It prevents the integration process from stopping due to temporary issues. Enabling this option will allow service calls to be retried upon certain types of failure. A service call may be retried up to 3 times before an exception is fired. Retries occur after 0 seconds, 15 seconds, and 60 seconds.

Warning: We have designed our retry feature carefully such that the retry should only occur when it is deemed safe to do so; however, in some occasions such retry service calls could result in the creation of duplicate data.

Proxy Mode

Proxy Mode option allows to specify how you want to configure the proxy server setting. There are three options available.

  • No Proxy
  • Auto-detect (Using system configured proxy)
  • Manual
Proxy Server

Using Proxy Server option allows you to specify the name of the proxy server for the connection.

Port

The Port option allows you to specify the port number of the proxy server for the connection.

Username (Proxy Server Authentication)

Username option (under Proxy Server Authentication) allows you to specify the proxy user account.

Password (Proxy Server Authentication)

Password option (under Proxy Server Authentication) allows you to specify the proxy user's password.

NOTE:  The Proxy Password is not included in the connection manager's ConnectionString property by default. This is by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be ProxyPassword=myProxyPassword;  (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

More Info page

The More Info page shows some basic information about the toolkit. In this page, you can find the version information of the toolkit.

NetSuite ssis connection manager more info

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 a 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 "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 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. 

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.

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 do 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 in to 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 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.

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.

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.

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. 

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 change cycle through common data types for that field.dd Button

add NetSuite custom field

When a common object is selected the Add button will be visible on the columns page. Clicking the add button will launch the 'Add Custom Field' dialog.  This dialog allows additional fields to be added that correspond to custom fields on the object. Fields can be added manually, detected automatically.  If the autodetect option is used the component will retrieve the specified number of records from NetSuite, and populate the grid with any custom fields found. 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' dropdown 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.

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

NetSuite ssis destination component

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 write 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 the 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.

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

Columns page

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

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

NetSuite ssis destination component coloumns

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

Add Button

add NetSuite custom field

When a common object is selected the Add button will be visible on the columns page. Clicking the add button will launch the 'Add Custom Field' dialog.  This dialog allows additional fields to be added that correspond to custom fields on the object. Fields can be added manually, detected automatically. If the autodetect option is used the component will retrieve the specified number of records from NetSuite, and populate the grid with any custom fields found.  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. 

NetSuite ssis destination component error handling

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 represent rows that were successfully written, and the red 'Error Output' connection represents rows that were erroneous. The 'NetSuiteErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by NetSuite or the component itself. 

NetSuite ssis destination component error output

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

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

License Manager

SSIS Integration Toolkit comes with a license manager program which helps you manage and activate the product license key to be used for the toolkit.

Without a commercial license, SSIS Integration Toolkit will operate under the Developer License which is free to use for development or evaluation purpose. Under the developer license, you can use the software within the development tool (SSDT-BI, BIDS, or Visual Studio). 

KingswaySoft License Manager

The only limitation with the free developer license is the inability to run the software outside of the development tool (SSDT-BI, BIDS, or Visual Studio). If you would like to run the software outside the development tool, such as running SSIS packages on a scheduled basis or from a command line, you will need to acquire a license from us.

If you want to test out the functionality by scheduling your SSIS packages, a trial license can be requested. To do so, you can launch License Manager program, then click "Change License Key" button, where you can request a free trial license after filling out the necessary Licensee Information.

KingswaySoft License Manager

If you have received a product license key from us after placing an order through our online shopping cart system, you can also click "Change License Key" button and enter the product license key in order to activate the software to use the fully-featured commercial license. 

To request a free trial license or activate a product license key that you have received, you can use Web Service option to complete the process by sending the request to our license server directly. An Internet connection is required when Web Service option is used. This is the option that we recommend.

Alternatively, you can choose the Email option so that the license manager will generate an email for you which you can send to us. The Email option should only be used if your system has no Internet access. It requires manual processing so please expect to wait for 24 to 48 hours before receiving a license file from us. Once you have received the license file from us from through email, you can save it to a local file, which you can then install by clicking "Install License File..." button in License Manager.

If you have acquired a perpetual license from us, once the software has been activated, your license manager will be shown as something like below.

KingswaySoft License Manager

You can see your Support Expiry Date or Maintenance Expiry Date in the License Manager program if you are using a perpetual license. By default, your perpetual license comes with a one-year maintenance and upgrade, which entitles you to use any version of the software released before your Support Expiry Date or Maintenance Expiry Date. You can extend it by entering a new maintenance license key that you have acquired from us.

KingswaySoft License Manager

If your commercial license is a subscription license, you will not see the Support Expiry Date or Maintenance Expiry Date option in the License Manager program, since your subscription license comes with maintenance and upgrade for the entire subscription period.

NOTE: You must run License Manager program under a local administrative account due to the privileges required to write license file to the system.

Contact Us

If you need any further assistance with the toolkit, please don't hesitate to contact us