Overview
Configuration is provided for establishing connections with NetSuite REST service. The configuration is used in the REST Connection Manager.
Setup
Press 
icon to get more information about the connection parameters.
Obtain data
Use REST Source component to get data from service resource.
Insert data
Use REST Destination component to insert data into service resource.
Quick Start
In this guide, we will show you step-by-step how to create a connection to NetSuite REST API using COZYROC's REST Connection Manager.
Step 1. Run "Visual Studio" under "Administrator account". Right click on Connection managers Tab and Select New Connection. In Add SSIS Connection Manager scroll down and select Connection managers for REST Connection and click Add.
Step 2. From drop-down menu Configuration select NetSuite.
Step 3. Set [accountID] to the desired accountID in the Server host parameter. In the grid below for Authentication parameter choose the desired Authentication Type.
Step 4. As an example we will setup TBA Authentication OAuth 1. For OAUTH 1 Authentication Type provide the values for the parameters in the grid below.
Step 5. Click "Test Connection" to test your connection.
In this guide, we will show how to read data from the NetSuite service resource using the COZYROC REST Source component.
Step 1. Select an existing "Data Flow task" or create a new "Data Flow Task".
Step 2. Open "SSIS Toolbox" and drag and drop COZYROC "REST Source" component.
Step 3.1 Read without SuiteQL Statement
Double click on the "REST Source" component and select NetSuite connection from the combo. Select from available NetSuite resources listed in the second combo below.
Optionally you can include extra parameters in the grid below if needed.
Step 3.2 Read with SuiteQL Statement
Double click on the "REST Source" component and select NetSuite connection from the combo. Select Resource Query from the dropdown list. Provide SuiteQL statement for the Query parameter in the grid below.
Step 4. Click on the "Columns tab" and select NetSuite resource columns you want to read.
Step 5. Click Preview to make sure all necessary resource columns are included.
Bravo! You have now learned how to read data from NetSuite service.
Configuration
Base URL address: https://[accountID].suitetalk.api.netsuite.com/services/rest.
- OAuth 2
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300) * 1000}}.The authentication has the following user-defined parameters:
- Scope:
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
Documentation: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_158074210415.html.
- OAuth 1
-
The authentication uses a parameters-based authentication.
The authentication has the following user-defined parameters:
- ConsumerKey: Required. Specify consumer key.
- ConsumerSecret: Required. Specify consumer secret.
- TokenId: Required. Specify token identifier.
- TokenSecret: Required. Specify token secret.
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
{{= OA1a.getAuthorizationHeader( { realm: s.replaceAll(new Uri(connection.serverHost).host().split('.')[0].toUpperCase(), '-', '_'), ConsumerKey: connection.user.ConsumerKey, ConsumerSecret: connection.user.ConsumerSecret, Access: connection.user.TokenId + '&' + connection.user.TokenSecret }, request ) }}
Documentation: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_157652390285.html.
Based on resource template Base.
- [Read] action
-
Endpoint URL address:
/query/v1/suiteql.
The result is extracted from:{{=response.items}}.
The action uses POST method.The action has the following user-defined parameters:
- Query: Required. Specify SuiteQL statement.
The following request parameters will be automatically processed:
-
Prefer:
transient -
application/json:
{{={ q: parameters.Query } }}
- [External]
-
The external fields URL address:
/query/v1/suiteql. The external fields list is extracted from:{{=response.items.length > 0 ? _.map(response.items[0], function(v, k) { return { name: k }; }) : []}}.- {{=external.name}}
Template: ShortText.
- [Read] action
-
Endpoint URL address:
/query/v1/suiteql.
The result is extracted from:{{=response.items}}.
The action uses POST method.The action has the following user-defined parameters:
- WhereClause: Specify WHERE clause. Optional.
The following request parameters will be automatically processed:
-
Prefer:
transient -
application/json:
{{={ q: 'SELECT ' + parameters.fields.join() + ' FROM ' + resource.external.name + (parameters.get('WhereClause') ? ' WHERE ' + parameters.WhereClause : '') } }}
Documentation: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_157909186990.html.
- [Read useless] action
-
Endpoint URL address:
/record/v1/{{=resource.external.name}}.
The result is extracted from:{{=response.items}}.The action has the following user-defined parameters:
- q: Specify N/Query statement. Optional.
Documentation: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_1540810951.html.
- id
A key field. Template: ShortText.
- [External]
-
The external fields URL address:
/record/v1/metadata-catalog/{{=resource.external.name}}. The external fields list is extracted from:{{=_.map( response['x-ns-filterable'].length > 0 ? response['x-ns-filterable'] : _.keys(response.properties), function(name) { return _.extend({ name: name }, response.properties[name]); } )}}.- {{=external.name.toLowerCase()}}
Data type: {{=NetSuite.getDataType(external.type)}} ( length 255 )
The external resource list URL address: /record/v1/metadata-catalog.
The external resource list is extracted from: {{=response.items}}.
Based on resource template Base.
- [Read] action
-
The result is paginated.The following request parameters will be automatically processed:
-
limit:
{{=parameters.batchSize}} -
offset:
{{=parameters.iterator}} -
_includeUserParameters:
{{=parameters}}
-
limit:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=item}}
-
_includeUserParameters:
- [Update] action
-
The action uses PUT method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=item}}
-
_includeUserParameters:
- [Delete] action
-
The action uses DELETE method.
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- Date
Data type: DT_DBDATE
Knowledge Base
What's New
- New: Introduced connection.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.