Disappointment has set in. After working on a project to move financial data from NetSuite to a local data warehouse, we discovered that there is a bug using SQL Server’s Linked Server technology through the NetSuite ODBC driver.
What’s important to note is that it may not be obvious there’s a bug. For the most part, everything appears to work. You can connect and perform queries without complaint. However, we discovered something that looked like a data integrity problem. It appeared that we had accounts within the posting_account_activity table that were not in the accounts table. Yet, if we performed a native ODBC query, we would get the expected result: all transactions accounts were referenced in the accounts table.
Upon a fair amount of head scratching, we discovered that there is a bug in the ODBC driver to Oracle. See this article: https://itknowledgeexchange.techtarget.com/itanswers/linked-server-query-returns-incomplete-results/
Turns out that Microsoft suggests that the SQL Server community connect to Oracle with Oracle drivers, not the ODBC drivers. That doesn’t help us because the drivers to connect with NetSuite are using DataDirect’s Open Access drivers.
At this point, we are devising a new solution to the way we retrieve NetSuite data which will likely require a custom SQL Server Integration Services package to pull down down using the ODBC driver direct.
See Related Articles
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
How has your new solution worked out? Any heads up insights to using SSIS to access NetSuite data?
Hi Scott,
The challenges are in the ODBC driver. I learned from DataDirect that NetSuite is solely responsible for the driver and any issues (bugs) need to be resolved by their team.
Here is how I am seeing it at this point in time:
1. Large downloads are likely going to be challenging to implement. Think trial-and-error. That may be acceptable in the initial data load.
2. Incremental downloads wil likely be okay if you keep the data set small.
3. Be sure to prepare for exceptions and plan to put energy in monitoring and resolution it until your reliability is proven.
What kind of project are you working on?
Marty
Our overall goal is to be able to build better reporting with the data.
Right now we are going down the road of pulling the data using the API instead of a DB connection. The toughest part about that right now is that the data is so highly normalized, and it doesn’t come down with explicit relationships.
I’ve considered just getting the NetSuite ODBC driver and using it just to quickly explore all the data and relationships, purely as a resource in our efforts to get the data through the API.
Scott,
The Web Services API has some limitations if you need to produce financial statements. Most, but not all, of the data is there. The ODBC connection generally is much easier for reporting and less expensive than rolling your own extraction layer.
A number of companies are making offers to get that data out via Web Services typically for migrating to other platforms and less for for reporting. Check out Explore Consulting, Sesame Software and Connection Cloud.
Let us know how it goes and if we can help,
Marty
Thanks Marty. Any data off the top of your head that isn’t available through the API?
Hi Scott,
Yes, read this article I wrote. I think it will provide the guidance you need:
https://blog.prolecto.com/2013/01/24/considering-producing-netsuite-financial-statements-using-third-party-tools/
If you need help, I have consultants that are expert in both NetSuite and database technologies.
Marty
Hey guys,
I have downloaded and setup the NetSuite ODBC driver. currently working out some firewall issues to be able to communicated with the netsuite server on port 1708. But my question is, after I have this all setup how do extract the data from NetSuite into my oracle database? Is there some kind of tool that I need to install to be able to write SQL queries?
Thanks for your help
Best Regards,
Hi Ziad,
After you get the driver properly setup, the NetSuite database can be queried with any tool that knows how to speak ODBC. For example, SQL Server Integration Services (SSIS) has an array of tools that allow you to connect to tables / queries and move the data to other databases. The world is rich with these types of tools. For example, Tableau (https://www.tableausoftware.com/) has a set of tools to help extract out data as well. You can also write you own with .NET.
Marty
Marty,
Thanks for the very helpful feedback. I finally have this setup but when I connect I get the message “This data source contains no visible tables”. Is there a switch or a configuration that needs to be done on NetSuite to expose the objects/tables?
Best Regards,
Mart,
Never mind I figured it out. There is an option to display system tables.
Best Regards,
Ziad
Is this still an issue? We’ve just started to move to NetSuite. We have a small Netsuite implementation with which we are using the api to pull data for our Datawarehouse and other intergration points. We are now moving to a full blown implementation and the odbc driver looked interesting until I found this post.
Hi Josh,
I recommend you move with a posture of caution. Since your database is presumably small, I don’t think you will have much issues. I recommend keeping things as simple as possible and avoid in use of extra functions on the SQL calls.
I also have connections to other parties that are doing a good job for others pulling down the entire NetSuite database in an ongoing fashion including two way sync. Be sure to read this article:
https://blog.prolecto.com/2012/08/31/extract-netsuite-data-for-backup-and-reporting/
Has this ODBC issue been resolved? Anyone aware of this?
Has this ODBC issue been resolved? Anyone aware of this?
Hiya, I’ve got a very odd issues that you may have come across and know a work around.
I use SSIS and the NetSuite ODBC driver to copy tables to a local SQL server.
No real problems, Transaction Lines is 1.5m records and takes 10 minutes. That’s 3 years of business.
My main problem is that I can’t see any of the data in the ACCOUNTS tables, and can’t see any Supplier Invoice transactions. My admin says I’m set up for everything, but it’s either bad permissionning or a bug.
Anyone seen that?
Thanks for your selfless help.
Tom,
Are you looking at the transaction_lines table? That’s where the supplier invoice is. The rest may have to do with permissions as NetSuite honors the hiding of data based on role?
Marty
Hiya, it was indeed permissions issues. I needed to be set up with access to all the ledgers in order to be able to see Supplier Invoices in Transactions / Transaction Lines via ODBC access. Now done, it’s very powerful to set up proper reporting outside the walled garden of the UI.
That’s great news! Thank you for the follow up.