Working with NetSuite in Talend Studio
The Talend Winter '17 Release offers 1000+ components for developers to create a wide variety of data integration, big data, mediation, and master data management ETL/ELT jobs. Many of these include connections to a variety of Enterprise Software products like Salesforce, SAP, and NETSUITE, the subject of this article. ORACLE completed the acquisition of the NetSuite software in November 2016 with claims of being the #1 Cloud ERP Enterprise Software solution supporting over 40,000 organizations.
Many of these companies use Data Integration tools like Talend Studio to build jobs that process data in and out of NetSuite.
As an 'Object-Based' package, NetSuite data processing can be either quite simple or become highly complex depending upon the use case. Crafting data integration jobs that can seamlessly join objects (like ACCOUNT or DEPARTMENT), is not always as straight forward as using SQL queries against multiple tables in a database.
Instead, NetSuite objects, as an abstraction layer, shields developers from the underlying physical schema implementation changes from one version to the next. This level of abstraction also protects ETL jobs where a new version can dramatically impact SQL queries dealing directly with the database requiring costly maintenance.
There are 3 basic methods for working with NetSuite in Talend Studio. They include:
- Using Talend/NetSuite components
- Using NetSuite OpenAir SOAP API calls & the
For more information about the tXMLMap component, see the Talend Components Reference Guide.
- Using JDBC connection adapters
Each of these data integration options impose different advantages and disadvantages. Picking the right one for a particular use case is essential. Still, they all fulfill the basic requirement to store and retrieve data, processing it within a Talend ETL/ELT Job. To decide which option to use it is important to understand how each works.
There are two NetSuite components currently available for developers in Talend. They follow common usage found with many other palette components in that there is a tNetsuiteInput to read data and an tNetsuiteOutput to write data.
For more information about the tNetsuiteInput component, see the Talend Open Studio for Big Data Components Reference Guide.
For more information about the tNetsuiteOutput component, see the Talend Components Reference Guide.
The main difference is that these components call the NetSuite SOAP API to retrieve and store data. They also provide additional capabilities that allow the selection and manipulation of NetSuite Objects.
Using these components is the recommended best practice, however they can be a bit tricky to use.
Using the tNetsuiteInput and the tNetsuiteOutput components
- First, the connection to NetSuite must be accurate. Enter the appropriate WSDL Endpoint, Username, Password, and Account Id within double quotes.
- Enter the Role as an integer (no quotes). Select a Record Type (NetSuite object) and ensure that date columns in its schema have a specified mask (ie: "yyyy-MM-dd HH:mm:ss").
- The input component provides an optional Search Criteria which can be set to limit the scope of data returned.
- The output object provides a die on error condition to control a conditional exit of the job in the event of an error which could disrupt continued execution.
- Ensure the Action on the object is set to
Insert (default), Update,
Delete, or Upsert operations
A common requirement when using the tNetsuiteInput component is the ability to join with another NetSuite object.
For example, consider the case where the ACCOUNT object, which contains a DEPARTMENT object column, needs to extract the Department Name for the output of the join.
Using the tMap component, and a second tNetsuiteInput component for the lookup, the NetSuite SOAP API must be utilized. The getInternalId() method can be used the extract the InternalId needed for the join.In the image above the ACCOUNT object is read as the main data flow and the DEPARTMENT object is the lookup. In the tMap component, the join expression is set to extract InternalId from the ACCOUNT.Department (row1) object in the DEPARTMENT object (row2) lookup.