NetSuite
Introducing the COZYROC SSIS+ library with support for integration and migration of NetSuite data. The NetSuite components are third-party plug-ins for Microsoft SSIS. Available on the market since 2012, COZYROC makes it easy to integrate or migrate data to and from NetSuite and will preserve the integrity of your data by always using the recommended SuiteTalk web services API. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for NetSuite data. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
The NetSuite integration package consists of four parts:
- NetSuite Connection Manager
- Stores the credentials and functionality for connecting to the NetSuite service.
- Well documented API for use by user-defined scripts.
- NetSuite Source component
- Retrieves data from a NetSuite record.
- SearchXML criteria may be used to selectively retrieve records.
- Handles composite records by setting up one output for the main records and a separate output for the related composite records.
- The Record and the SearchXML criteria can be modified at runtime using variables and an expression.
- NetSuite Destination component
- Writes data to NetSuite records.
- Supports four actions on Objects: Create, Update, Delete or Upsert.
- Provides information about records with errors as well as the record identifier for records that were added, updated or deleted.
- The component can load composite records into the NetSuite service.
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional NetSuite integration scenarios.
In this section we will show you how to set up a NetSuite Source component.
- Before you begin, configure a NetSuite connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the NetSuite Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, in the Connection Managers tab, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab.
- For InputType, either select Record or SearchXML.
- If you selected Record, then select the record you want to work with from the drop-down list. Then jump to step 13.
- If you selected Statement, then click on the ellipsis next to the SearchXML parameter.
- Use the aids in the pop-up window to compose a SearchXML statement to retrieve the entity and records you want from the source. Alternatively, you may type your own SearchXML statement into the pop-up window. The statement should use the same XML format that is used in the NetSuite search method.
- Click OK to close the pop-up window.
- Click OK to close the component editor.
Congratulations! You have successfully configured the NetSuite Source component.
In this section we will show you how to set up a NetSuite Destination component.
- Before you begin, configure a NetSuite connection manager.
- Configure a source component that will provide the data to be written to NetSuite.
- Ensure that you are on the Data Flow canvas.
- In the SSIS Toolbox, locate the NetSuite Destination component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, select the connection manager you configured earlier from the Connection drop-down list.
- Choose the desired Action (Create, Update, Delete, or Upsert). In this example, we chose the Create action.
- Choose the Record you will be working with. We will work with the Contact record in this example.
- Click OK to close the component editor.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the NetSuite Destination component.
- An Input Output Selection window will pop up. Under Input, select the specific record you want to direct the output to.
- In this example, we chose Contact.
- Click OK to close the Input Output Selection window.
Congratulations! You have successfully configured the NetSuite Destination component.
In this section we will show you how to set up a NetSuite Connection Manager to access your NetSuite service.
- Right-click on the Connection Manager canvas.
- Select New Connection.
- When the list of connection managers appears, expand the Type column, if necessary, so you can see the complete names of the connection managers.
- Scroll down the list to find the NETSUITE connection manager and select it.
- Once the connection manager dialog opens, ensure the Authentication Type is set to Session. This is the default.
- Enter your User name, which is an email address associated with your NetSuite account.
- Enter your Password.
Locate or generate an Application ID.
- Log in to NetSuite.
- Go to Setup > Integration > Manage Integrations and click on Manage Integrations.
- If an application for COZYROC shows up in the list, copy the Application ID associated with it and skip to step 15.
- If there is no existing application in the list associated with COZYROC, Go to Setup > Integration > Manage Integrations > New and click on New.
- Enter a Name for the COZYROC SSIS+ integration tool.
- Make sure the box for Token-Based Authentication is unchecked and only the box for User Credentials is checked. Click Save.
- NetSuite will generate an Application ID. Copy the Application ID just created by NetSuite.
- Paste the Application ID into the Application ID field in the connection manager dialog.
Locate your Account ID.
- In NetSuite, Navigate to Setup -> Integration -> Web Services preferences and click on Web Services Preferences.
- Locate the ACCOUNT ID field and copy it.
- Paste the Account ID into the Account ID field in the connection manager dialog.
- Press Test Connection.
- When you see Test Connection succeeded, click OK.
- Click OK to close the connection manager dialog.
- Congratulations! You have successfully configured a NetSuite connection manager.
Overview
NetSuite Connection Manager is an SSIS Connection Manager for establishing NetSuite connections. Use the API to build components based on the NetSuite Connection Manager. We recommend using NetSuite Web Services Usage Log when designing solutions.
Quick Start
In this section we will show you how to set up a NetSuite Connection Manager to access your NetSuite service.
- Right-click on the Connection Manager canvas.
- Select New Connection.
- When the list of connection managers appears, expand the Type column, if necessary, so you can see the complete names of the connection managers.
- Scroll down the list to find the NETSUITE connection manager and select it.
- Once the connection manager dialog opens, ensure the Authentication Type is set to Session. This is the default.
- Enter your User name, which is an email address associated with your NetSuite account.
- Enter your Password.
Locate or generate an Application ID.
- Log in to NetSuite.
- Go to Setup > Integration > Manage Integrations and click on Manage Integrations.
- If an application for COZYROC shows up in the list, copy the Application ID associated with it and skip to step 15.
- If there is no existing application in the list associated with COZYROC, Go to Setup > Integration > Manage Integrations > New and click on New.
- Enter a Name for the COZYROC SSIS+ integration tool.
- Make sure the box for Token-Based Authentication is unchecked and only the box for User Credentials is checked. Click Save.
- NetSuite will generate an Application ID. Copy the Application ID just created by NetSuite.
- Paste the Application ID into the Application ID field in the connection manager dialog.
Locate your Account ID.
- In NetSuite, Navigate to Setup -> Integration -> Web Services preferences and click on Web Services Preferences.
- Locate the ACCOUNT ID field and copy it.
- Paste the Account ID into the Account ID field in the connection manager dialog.
- Press Test Connection.
- When you see Test Connection succeeded, click OK.
- Click OK to close the connection manager dialog.
- Congratulations! You have successfully configured a NetSuite connection manager.
Parameters
Server
Use the Server page of the NetSuite Connection Manager dialog to specify properties for connecting to NetSuite service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify authentication type. The following options are available:
Value Description Session Use user name and password session authentication. Token Use token-based authentication. For information how to enable token-based authentication, check here. Specify user name to access NetSuite.
Specify password to access NetSuite.
Specify account identifier. To find your identifier follow these instructions:
- Login to the NetSuite portal.
- Navigate to Setup -> Integration -> Web Services preferences
- Locate ACCOUNT ID field.
Specify role to access NetSuite. If you don't specify a role, the user's default role is used. Check below list of standard roles:
Value Role 1 Accountant 2 Accountant (Reviewer) 3 Administrator (default) 4 A/P Clerk 5 A/R Clerk 6 Bookkeeper 7 CEO (Hands Off ) 8 CEO 9 Sales Manager 10 Sales Person 11 Store Manager 12 Support Manager 13 Support Person 14 Customer Center 15 Employee Center 16 Vendor Center 18 Full Access 19 Warehouse Manager 20 Payroll Manager 22 Intranet Manager 23 Marketing Manager 24 Marketing Assistant 25 System Administrator 26 Sales Administrator 27 Support Administrator 28 Marketing Administrator 30 NetSuite Support Center Specify application identifier to access NetSuite. To generate new application identifier follow these steps:
- Log in to NetSuite.
- Go to Setup > Integration > Manage Integrations > New.
- Enter a Name for the application that will be integrating with NetSuite.
- Make sure the box for Token-Based Authentication is unchecked and only the box for User Credentials is checked. Click Save.
- Copy-and-paste the generated Application ID.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection. Specify consumer key used in token-based authentication.
Specify consumer secret used in token-based authentication.
Specify token identifier used in token-based authentication.
Specify token secret used in token-based authentication.
Advanced
Use the Advanced page of the NetSuite Connection Manager dialog to to specify additional properties if your server configuration is different from the default.
Specify NetSuite service URL. This property should be explicitly specified when doing sandbox testing. Sample URLs:
- https://webservices.sandbox.netsuite.com/services/NetSuitePort_2011_1
Proxy
Use the Proxy page of the NetSuite Connection Manager dialog to specify properties if your server is behind firewall.
Specify the name or IP address of the proxy server. If not specified, proxy server is not used.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
FAQ
We recommend using the Fiddler tracing application. Make sure you enable HTTPS tracing option before you start your trace.
What's New
- New: Updated web service proxy to version 2018.2.
- New: Support for token-based authentication.
- New: Updated web service proxy to version 2017.2.
- New: Support for automatic renewal of expired session.
- New: Updated web service proxy to version 2016.2.
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- New: Updated web service proxy to version 2015.1.
- New: Updated web service proxy to version 2014.1.
- New: Updated web service proxy to version 2013.1.
- New: Updated web service proxy to version 2012.2.
- Fixed: Failed with error "Your connection has timed out. Please log in again." when trying to connect.
- New: Introduced connection.
Related documentation
Overview
The NetSuite Source Component is an SSIS Data Flow Component for consuming data from the NetSuite Service. The configuration parameters are provided in the standard data flow source component dialog. A separate output is setup for the main record and each related composite record.
Demonstration
Quick Start
In this section we will show you how to set up a NetSuite Source component.
- Before you begin, configure a NetSuite connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the NetSuite Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, in the Connection Managers tab, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab.
- For InputType, either select Record or SearchXML.
- If you selected Record, then select the record you want to work with from the drop-down list. Then jump to step 13.
- If you selected Statement, then click on the ellipsis next to the SearchXML parameter.
- Use the aids in the pop-up window to compose a SearchXML statement to retrieve the entity and records you want from the source. Alternatively, you may type your own SearchXML statement into the pop-up window. The statement should use the same XML format that is used in the NetSuite search method.
- Click OK to close the pop-up window.
- Click OK to close the component editor.
Congratulations! You have successfully configured the NetSuite Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing NetSuite connection manager.
Related Topics: NetSuite Connection Manager
Specify the number of rows to be retrieved as a batch. Default is 0. If set to 0, the page size defined in NetSuite search preferences will be used.
Specify input type. This property has the options listed in the following table.
Value Description Record Input type is NetSuite record or composite object. Selecting this value displays the dynamic options Record. SearchXML Input type is search statement. Selecting this value display the dynamic options SearchXML. After input change, you have to use Refresh command in the 'Column Mappings' tab to reload source object metadata.Select NetSuite record or composite object.
Specify NetSuite search record statement. The statement should be the same XML format used in NetSuite search method. For example to retrieve all account records containing the keyword "Drawing" in account's name, use the following statement:
<AccountSearchBasic> <name operator="contains"> <searchValue>Drawing</searchValue> </name> </AccountSearchBasic>
FAQ
The composite record output contains not only fields from the composite record, but also the parent records. The output will be populated with sets of rows, one set for each parent record. Each set of rows is separated with blank row (all NULLs).
For additional information about modelling of composite records in SSIS+, please refer to this article.
The source record or statement are available in the data flow properties list. Follow these steps to setup expression:
- Right-click on data flow canvas and select Properties menu.
- Scroll-down and find property named like [NetSuite Source].[SearchXML]. This is the property containing the record statement.
- Scroll-down and find Expressions property. Setup expression to modify the record statement dynamically.
The sales order is found under the general transactions record. You have to specify SearchXml statement to retrieve them. For sales order specifically try the following statement:
<TransactionSearchBasic> <type operator="anyOf"> <searchValue>_salesOrder</searchValue> </type> </TransactionSearchBasic>
What's New
- Fixed: Failed to process transaction item custom fields (Thank you, Vijay).
- Fixed: Component failed when using saved search against custom record (Thank you, Mark).
- Fixed: Component failed with error "The maximum number (1000) of records allowed for a READ operation has been exceeded" when having more than 1000 customizations (Thank you, Sam).
- Fixed: NetSuite's pricingMatrix composite object was not available for use (Thank you, Rahim).
- New: Component now supports saved searches.
- New: Redesigned for better support of composite data.
- New: Introduced component.
Related documentation
Overview
NetSuite Destination Component is SSIS Data Flow Component for loading data into NetSuite record. The configuration parameters are provided in the standard data flow destination component dialog. A separate input is setup for the main record and each related composite record.
Demonstration
Quick Start
In this section we will show you how to set up a NetSuite Destination component.
- Before you begin, configure a NetSuite connection manager.
- Configure a source component that will provide the data to be written to NetSuite.
- Ensure that you are on the Data Flow canvas.
- In the SSIS Toolbox, locate the NetSuite Destination component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, select the connection manager you configured earlier from the Connection drop-down list.
- Choose the desired Action (Create, Update, Delete, or Upsert). In this example, we chose the Create action.
- Choose the Record you will be working with. We will work with the Contact record in this example.
- Click OK to close the component editor.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the NetSuite Destination component.
- An Input Output Selection window will pop up. Under Input, select the specific record you want to direct the output to.
- In this example, we chose Contact.
- Click OK to close the Input Output Selection window.
Congratulations! You have successfully configured the NetSuite Destination component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing NetSuite connection manager.
Related Topics: NetSuite Connection Manager
Select destination record action. This property has the options listed in the following table.
Action Description Create Create new instance in the destination NetSuite record. Update Update existing instance in the destination NetSuite record. You have to specify internalId for the update to work. Delete Delete existing instance from the destination NetSuite record. Upsert Update and insert record in the destination NetSuite record. You have to specify externalId for action to work. After action change, you have to use Refresh command in the 'Column Mappings' tab to reload destination page metadata.Specify the destination NetSuite record where the data is loaded.
Specify the number of rows to be sent as a batch.
Specify how to handle rows with errors.
Contains the unique identifier of the added, updated or deleted record.
FAQ
The composite record input accepts sets of rows, one set for each parent record. Each set of rows must be separated with blank row (all NULLs). The parent record will not finish processing its record, until all composite record children have received blank row to indicate completion. You can use Query transform to prepare child records with the proper blank rows.
For additional information about modelling of composite records in SSIS+, please refer to this article.
The component's Error Output is used for providing both error information and the processed record identifier. Check the ErrorCode column:
ErrorCode Description -1 Not an error record. >0 Error record. Check the ErrorDescription column for more details. You can set up a standard Conditional Split component to filter non-error records.
The input for list reference field is a string containing both list type identifier and record internal identifier. The input format is:
<type id>;<internal id>
Multiple list references are separated with semicolon (;).
What's New
- Fixed: Component failed to set custom fields to NULL (Thank you, Dave).
- Fixed: Failed to process transaction item custom fields (Thank you, Vijay).
- Fixed: Component failed to delete records from custom record type (Thank you, Mark).
- Fixed: NetSuite's pricingMatrix composite object was not available for use (Thank you, Rahim).
- New: Redesigned for better support of composite data.
- New: Introduced component.
Related documentation
Overview
These public interfaces are used in NetSuite components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll and CozyRoc.NetSuite.dll libraries. Also include CozyRoc.SqlServer.SSIS, CozyRoc.NetSuite and CozyRoc.NetSuite.Service namespaces.
IConnection / INetSuiteConnection
When you call NetSuite Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with NetSuite service.
Connects to NetSuite service. The method result is object implementing NetSuite SuiteTalk web services.
Closes the connection to NetSuite service. Use always after you finish working with the service.
Returns true if connected to the service.
Get NetSuite web service object.
Returns list of available NetSuite records for specific operation. These are the method parameters:
- operation (uint) - It can be one of the options listed in the following table.
Value Description 0 Return records, which can be read. 1 Return records, which support adding. 2 Return records, which can be updated. 3 Return records, which support remove. 4 Return records, which support upsert.
Gets specified record composite records list. These are the method parameters:
- root (string) - record name.
Get object used to manage NetSuite record. The method returns IRecord object. These are the method parameters:
- record (object) - record name or NetSuite Record object.
Get search result for specified record. The method returns SearchResult object. These are the method parameters:
- record (string) - record name.
- criteria (IDictionary) - optional. Set to retrieve records for specified criteria.
Retrieves records from NetSuite service. These are the method parameters:
- criteriaXML (string) - Specify NetSuite search record statement. The statement should be the same XML format used in NetSuite search method.
- validateOnly (bool) - if true, the specified search statement is validated and no search against the service is performed.
Get object used to manage NetSuite search row. The method returns ISearchRow object. These are the method parameters:
- searchRow (object) - NetSuite SearchRow object.
Returns array of saved search identifiers for specified type. These are the method parameters:
- searchType (Type) - type object.
Properties
Specify NetSuite service URL. This property should be explicitly specified when doing sandbox testing.
Specify user name to access the NetSuite service.
Specify user password to access the NetSuite service.
Specify accountID to access the NetSuite service.
Specify role to access the NetSuite service. If you don't specify a role, the user's default role is used.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
| Value | Description |
|---|---|
| True | All clients use same connection (default). |
| False | Every client uses different connection. |
Specify the name or IP address of the proxy server.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
IField / INetSuiteField
This interface is used to retrieve NetSuite record field information.
Properties
Returns field name.
Returns true if field is record key.
Returns field type.
Returns field length.
Returns field precision.
Returns field scale.
IRecord / INetSuiteRecord
This interface is used for NetSuite record management.
Methods
Returns the NetSuite RecordType.
Get record fields. The method returns array of IField objects.
Constructs new in-memory NetSuite record instance.
Sets field value in a record. These are the method parameters:
- record (object) - record instance.
- field (string) - field name.
- value (object) - field value.
Returns field value from record object. These are the method parameters:
- record (object) - record instance.
- field (string) - field name.
Retrieves records of composite object. These are the method parameters:
- record (object) - instance to main NetSuite record object.
- id (string) - composite object identifier. Multiple identifiers are separated with semicolon (;).
Updates composite object. These are the method parameters:
- record (object) - NetSuite record object.
- id (string) - composite object identifier. Multiple identifiers are separated with semicolon (;).
- list (object array) - array of composite NetSuite objects.
Properties
Returns the NetSuite record name.
Returns true if composite object.
ISearchRow 1.6 SR-2
This interface is used for NetSuite search row.
Methods
Get search row fields. The method returns array of IField objects.
Returns field value from search row object. These are the method parameters:
- searchRow (object) - searchRow instance.
- field (string) - field name.
Properties
Returns the NetSuite record name.
Related documentation
NetSuite Connection
- New: Updated web service proxy to version 2018.2.
- New: Support for token-based authentication.
- New: Updated web service proxy to version 2017.2.
- New: Support for automatic renewal of expired session.
- New: Updated web service proxy to version 2016.2.
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- New: Updated web service proxy to version 2015.1.
- New: Updated web service proxy to version 2014.1.
- New: Updated web service proxy to version 2013.1.
- New: Updated web service proxy to version 2012.2.
- Fixed: Failed with error "Your connection has timed out. Please log in again." when trying to connect.
- New: Introduced connection.
NetSuite Destination
- Fixed: Component failed to set custom fields to NULL (Thank you, Dave).
- Fixed: Failed to process transaction item custom fields (Thank you, Vijay).
- Fixed: Component failed to delete records from custom record type (Thank you, Mark).
- Fixed: NetSuite's pricingMatrix composite object was not available for use (Thank you, Rahim).
- New: Redesigned for better support of composite data.
- New: Introduced component.
NetSuite Source
- Fixed: Failed to process transaction item custom fields (Thank you, Vijay).
- Fixed: Component failed when using saved search against custom record (Thank you, Mark).
- Fixed: Component failed with error "The maximum number (1000) of records allowed for a READ operation has been exceeded" when having more than 1000 customizations (Thank you, Sam).
- Fixed: NetSuite's pricingMatrix composite object was not available for use (Thank you, Rahim).
- New: Component now supports saved searches.
- New: Redesigned for better support of composite data.
- New: Introduced component.
NetSuite Connection
We recommend using the Fiddler tracing application. Make sure you enable HTTPS tracing option before you start your trace.
NetSuite Destination
The composite record input accepts sets of rows, one set for each parent record. Each set of rows must be separated with blank row (all NULLs). The parent record will not finish processing its record, until all composite record children have received blank row to indicate completion. You can use Query transform to prepare child records with the proper blank rows.
For additional information about modelling of composite records in SSIS+, please refer to this article.
The component's Error Output is used for providing both error information and the processed record identifier. Check the ErrorCode column:
ErrorCode Description -1 Not an error record. >0 Error record. Check the ErrorDescription column for more details. You can set up a standard Conditional Split component to filter non-error records.
The input for list reference field is a string containing both list type identifier and record internal identifier. The input format is:
<type id>;<internal id>
Multiple list references are separated with semicolon (;).
NetSuite Source
The composite record output contains not only fields from the composite record, but also the parent records. The output will be populated with sets of rows, one set for each parent record. Each set of rows is separated with blank row (all NULLs).
For additional information about modelling of composite records in SSIS+, please refer to this article.
The source record or statement are available in the data flow properties list. Follow these steps to setup expression:
- Right-click on data flow canvas and select Properties menu.
- Scroll-down and find property named like [NetSuite Source].[SearchXML]. This is the property containing the record statement.
- Scroll-down and find Expressions property. Setup expression to modify the record statement dynamically.
The sales order is found under the general transactions record. You have to specify SearchXml statement to retrieve them. For sales order specifically try the following statement:
<TransactionSearchBasic> <type operator="anyOf"> <searchValue>_salesOrder</searchValue> </type> </TransactionSearchBasic>
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.