Overview

Extracting data from NetSuite can be a difficult process. This article goes through some issues and a solution for data extraction from NetSuite. 

The Issues

To extract data from NetSuite, we often encounter two key issues:

1. Field length. When creating a NetSuite connection type, the conversion from VARCHAR2 results in the length of fields being doubled when using #LENGTH# as the New Data Type Length. If the source field’s length is greater than 2000, then the target length becomes greater than 4000, which results in an error when the table is created in SQL Server by Collect. 
2. Schema Owner. NetSuite defines the Schema at the ODBC connection level. The issue is that since the schema owner is a required field, it gets in the way of Collect. Specifying a schema owner breaks the Source SQL when downloading the tables. An unspecified error is also thrown if all fields are specified in the Source SQL (Error ticket# kobks72phl2c0r80hwst[400]), for which there is no documentation. Selecting all columns using the * wildcard works correctly.

The Solution

To work around these issues, a stored procedure must be created and registered on the target SQL Server database (sdbNetSuite, for example) that cycles through all NetSuite packages and changes the Source SQL to SELECT * FROM <TABLENAME>. The stored procedure is reusable, but it must be executed again if new tables are added.

1. Create a new connection type
2. Create a stored procedure
3. Test the stored procedure
4. Extract data from NetSuite

Create a New Connection Type

Since there are  OA% objects for Tables and Columns, create a new connection type named ODBCNETSUITE, with the following Download SQL and Download Table values:

Download SQL:

SELECT '#SINGLEQUOTEESCAPEDSOURCE#' AS Source, TABLE_NAME AS TableName, TABLE_OWNER AS SchemaOWNER, ROW_NUMBER() OVER (ORDER BY TABLE_NAME, COLUMN_NAME) AS COLUMNORDER, COLUMN_NAME AS ColumnName, TYPE_NAME AS DataType, OA_LENGTH AS DataLength, OA_PRECISION AS DataPrecision, OA_SCALE AS DataScale, OA_NULLABLE as DATANULLABLE FROM OA_COLUMNS WHERE TABLE_NAME = UPPER('#SINGLEQUOTEESCAPEDSOURCETABLE#') AND COLUMN_NAME NOT LIKE 'SYS%' ORDER BY 1 ASC, 2 ASC, 3 ASC

Download Table:

SELECT DISTINCT TABLE_OWNER AS SchemaOWNER, TABLE_NAME "TABLENAME", 'Download Table' "Description" FROM OA_TABLES WHERE TABLE_OWNER <> 'SYS' AND TABLE_OWNER <> 'SYSTEM' ORDER BY 1 ASC, 2 ASC

NOTE: The other OA table is only for foreign keys and is therefore not relevant to download

data from Collect. The only two queries populated in the connection type are the Download SQL and the Download Table. 

Create a Stored Procedure

Create a new stored procedure, ideally, in a non-delivered database so it is not deleted with a Stewardship Tier upgrade. After all the tables are added, execute the stored procedure once in SQL (it does not need to be registered to the Stewardship Tier). If new tables are added in the future, execute the stored procedure again. 

Create the following new stored procedure:

DECLARE @Tablename NVARCHAR(255)

DECLARE @PackageName NVARCHAR(255)

DECLARE pcursor CURSOR FOR

SELECT PackageName FROM DataGarage.dbo.dgTargetSourceTable

WHERE [Target] = 'sdbNetSuite'

AND [Source] = 'NetSuite'

OPEN pcursor

FETCH NEXT FROM pcursor INTO @PackageName

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @Tablename = [Table] FROM DataGarage.dbo.dgTargetSourceTable

WHERE PackageName = @PackageName

UPDATE CranPort.dbo.CPPackage

SET SourceSQL = 'SELECT * FROM ' + UPPER(@TableName)

WHERE PackageName = @PackageName

FETCH NEXT FROM pcursor INTO @PackageName

END

CLOSE pcursor

DEALLOCATE pcursor

NOTE: sdbNetSuite and NetSuite are the Target and Source names registered in Collect. Please update these to match your specific case.

Test the Stored Procedure 

To test the stored procedure:

1. Register two tables in Collect. It is recommended to use Account and Activity because these are standard views (they are actually views, not tables) in NetSuite. However, any tables can be registered. 
2. Build the packages successfully. 
3. Run the new stored procedure. 
4. Refresh the tables immediately after running the stored procedure. 
5. Verify that both tables refresh successfully. 

Extract the Data from NetSuite

Once the stored procedure has been tested with successful outcomes, extract the data from NetSuite.

To extract data from NetSuite:

1. Obtain the full list of tables from the client that are needed. 
2. Add the tables to Collect. 
3. Build all the packages for the Target Source.
4. Run the stored procedure once. 
5. Refresh the tables. 

What if the Client Needs to Avoid Setting the Data Types to NVARCHAR (MAX)?

If the client needs to avoid setting data types to NVARCHAR (MAX), use the following data types instead:

Additional Notes:

• There's a new conversion behavior for the WVARCHAR data type. For lengths greater or equal to 2001, the new data type is NVARCHAR(4000) (Fixed 4000). For lengths of less than or equal to 2000, the Connection Type shows NVARCHAR(#LENGTH#), but keep in mind that the actual resulting data type in SQL SERVER is NVARCHR(#2XLENGTH#). This means that if NetSuite shows WVARCHAR(5) for a column, SQL Server has NVARCHAR(10), except for WVARCHAR(2001) or greater, which is NVARCHAR(4000) in every case. This is because WVARCHAR is supposed to be for wide variable length characters, so the length relation of NVARCHAR to WVARCHAR is 1:2. This is the change that eliminates the NVARCHAR(MAX). Instead, NVARCHAR(4000) is used when necessary (i.e., #LENGTH# > 2000).

• Any other data type currently translates to NVARCHAR(#LENGTH#) including regular character data types like CHAR, VARCHAR and VARCHAR2.

Apply Data Type Conversion

If data types were converted, apply the data type conversion:

1. Verify the Source Connection Type is set to the connection type created in Create a New Connection Type.
2. Run the following to insert the connection types instead of having to manually add them in the Stewardship Tier: 

BEGIN TRAN

DELETE DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion

WHERE [SourceConnectionType] = 'ODBCNETSUITE'

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '600', 'Data Type Match Only', 'TIMESTAMP', '0', '0', '0', 'Accept', 'DATETIME', 'DATETIME', '0', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 14:34:00', 'Administrator', '2021-05-13 14:34:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '650', 'Data Type Match Only', 'SMALLINT', '0', '0', '0', 'Accept', 'SMALLINT', '0', '0', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 13:56:00', 'Administrator', '2021-05-13 13:56:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '700', 'Data Type Match Only', 'INTEGER', '0', '0', '0', 'Accept', 'INT', '0', '0', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 13:55:00', 'Administrator', '2021-05-13 13:55:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '750', 'Data Type Match Only', 'DOUBLE', '0', '0', '0', 'Accept', 'FLOAT', 'FLOAT', '0', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 13:55:00', 'Administrator', '2021-05-13 13:55:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '800', 'Data Type Match Only', 'CLOB', '#LENGTH#', '0', '0', 'Accept', 'VARBINARY', 'VARBINARY', '0', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 13:54:00', 'Administrator', '2021-05-13 13:54:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '850', 'Data Type Match Only', 'CHAR', '#LENGTH#', '0', '0', 'Accept', 'NVARCHAR(#LENGTH#)', 'NVARCHAR', '#LENGTH#', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 13:53:00', 'Administrator', '2021-05-13 13:53:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '900', 'Data Type Match Only', 'BIGINT', '#LENGTH#', '#PRECISION#', '#SCALE#', 'Accept', 'BIGINT', 'BIGINT', '0', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 13:44:00', 'Administrator', '2021-05-13 13:52:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '950', 'Greater Than or Equal Maximum Length', 'WVARCHAR', '2001', '0', '0', 'Accept', 'NVARCHAR(4000)', 'NVARCHAR', '4000', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 14:04:00', 'Administrator', '2021-05-13 14:38:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '960', 'Data Type Match Only', 'WVARCHAR', '#LENGTH#', '0', '0', 'Accept', 'NVARCHAR(#LENGTH#)', 'NVARCHAR', '#LENGTH#', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-13 14:17:00', 'Administrator', '2021-05-13 14:40:00', '0', 'UserInterface', 'UserInterface')

INSERT INTO DataGarage.dbo.ztTargetSourceConnectionTypeDataConversion VALUES ('SQLSERVER', 'ODBCNETSUITE', '999', 'Default for any Data Type', 'ANY', '#LENGTH#', '0', '0', 'Accept', 'NVARCHAR(#LENGTH#)', 'NVARCHAR', '#LENGTH#', '0', '0', ' ', ' ', '0', 'Administrator', '2021-05-07 10:15:00', 'Administrator', '2021-05-13 13:32:00', '0', 'UserInterface', 'UserInterface')

Verify that the changes look correct in SSMS and then perform the following:

COMMIT TRAN