Get Data from NetSuite using Azure Data Factory. As Netsuite is an oracle product, getting data into your data warehouse can be a little tricky. Here is a helpful guide to get started.
Side Note: Want to learn SQL or Python for free. In less then 10 minutes a day and less than an hour total? Signup for my free classes delivered daily right to your email inbox for free!
Back to the article…
Helpful Links
https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_3994744300.html
1. Setup VM
Virtual Machine will be required since a server with the ability to install the correct ODBC drivers and a SQL Server to create the linked services will be needed. This will allow you to install an Integration runtime for Azure Data Factory to connect to and leverage the Linked Server
- Items to install on VM
- SQL Express Local DB
- Needed to test and verify ODBC connection to NetSuite and created Linked Server Connection
- Install the SQL Server as a Mixed Authentication Mode
- Create a User on SQL Server that has rights to all databases and linked servers needed in the final steps
- SQL Server Management Studio
- Needed to test and verify Linked Server ODBC connection to NetSuite
- Azure Data Factory Integration Runtime
- Needed to connect a VM to Data Factory
- NetSuite ODBC Driver for Windows
- See next steps for download and setup instructions
- SQL Express Local DB
2. Setup the NetSuite ODBC driver for Windows
You will need to install the NetSuite ODBC drivers on the VM so that it can communicate with the NetSuite Servers
- This will be provided by your Admin or Via the NetSuite Portal
- You will need to obtain the server host, service port, service data source, account ID and Role ID from NetSuite as well as configure a user that has access to these settings.
- NetSuiteODBCDrivers_Windows64bit
- Navigate to your ODBC Settings in Windows and Add a new System DSN. If everything installed correctly you will now see a “NetSuite Drivers 64bit” to select from.
- In the Settings for this page, enter the Account ID, and Role ID from the setup instructions from Netsuite
- Once you select “Test Connect” you will be prompted to enter your credentials you configured in NetSuite.
3. Setup a Linked Server Connection
With SQL Server Express and SQL Server Management Studio Setup, you can now create a Linked Server Connection to NetSuite.
- In the General tab, enter the following information:
- Linked server: NETSUITE (Name which you want to appear in the object explorer)
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Product name: NetSuite.com
- Data Source: The name of you 64-Bit ODBC Data Source from Previous Step. This was “NetSuite” in this example.
- Provider string: DSN=NetSuite.com
- In the Security tab, select “Be made using this security context:”. For remote login, use your NetSuite login and password.
- The linked server should now appear in the object explorer.
- you can now query the tables with queries similar to “Select * from [NETSUITE].[Database].[Administrator].[ACCOUNT_ACTIVITY]”
4. Set Up Integration Runtime in Azure Data Factory
Now that a linked server has been set up from your VM to the NetSuite box, you can now use this connection in Azure Data Factory. Since this is considered a on-premise server, you will need to install an Azure Integrated Runtime so that Azure Data Factory which is on the cloud can find and communicate with this VM.
5. Setup and Install Integration Runtime on VM
You now will need to install the Integration Runtime on the VM and configure it so that it can find and communicate with the Azure Data Factory Service you configured in previous step
If all was done correctly, you will now see a valid connection inside Azure Data Factory
6. Azure Data Factory Setup
Everything should now be setup and to move data from your NetSuite to your destination in Azure Data Factory