extracting cloud based netsuite data from the cloud in ssis
-
Hi we are going to need to extract netsuite data from the cloud. This is by no means big data and pbi doesnt yet have connectors so i believe targeting ssis experts is a good idea. does the community have any advice for reaching up from on prem ssis to the cloud and communicating with netsuite? right now all we are interested in is sales and 5 or 6 sales supporting dimensions. Here is a link where i asked the pbi community first https://community.fabric.microsoft.com/t5/Service/apis-and-netsuite/m-p/3831955/thread-id/229080#M229107
-
Don't know anything about Netsuite, but if there's a REST API, then you can definitely do this in SSIS.
The trickiest part of setting that up will probably configuring the OATH authentication, that bit is often a pain. If you can find a C# snippet which helps you with the authentication, everything else should be a case of following the API documentation.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
thx Phil. I had a meeting today with one of our cloud architects. Somehow our company (maybe using something called MS Graph) has a list of their api's in DEVOPS and can grant permissions on those api's. I asked him if he thought once an approval was given if its just a matter of maybe copying it and adding an assembly reference to the ssis project. He thinks yes. We are both hoping that GAC isnt relevant. Stay tuned.
-
If writing your own stuff is not what you are looking to do, then the next best bet is a 3rd party tool. SSIS doesn't natively connect to NetSuite. But a sample 3rd party tool that can help is CData. I do not work for them and have only used the trial version of their tools, but they are pretty nifty and can bake right into SSIS to pull data out of cloud systems including netsuite.
Link - https://www.cdata.com/ssis/
It is NOT a free tool mind you and if any admins or anything think it is spam, I apologize. I am NOT trying to sell stuff, I am just trying to answer the original question.
CData also solves the problem of connecting to Power BI as it has Power BI connectors to connect to NetSuite. So no need to pull the data on-prem first.
I am sure there are other tools out there that can do this too, this was just one I had come across that seems to cover a LOT of differnt data connections.
Another advantage to using 3rd party tools instead of building your own with the REST API is that the 3rd party tools generally keep up with API changes. Building it yourself, you need to watch each upgrade in case they remove or change the way an API call you are using works. You still need to test the 3rd party tools, but won't need to do any API rewrites. May just need to re-map some data points or may need to do no changes.
Downside is that you are going to be stuck in the license for a while after you sign up as it is a subscription based license. Stop paying, and your integrations will fail. Build it yourself and you have no licensing costs, just development costs and support costs.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
thx Brian. That is useful info. I'm going to price it.
Phil, because our ssis architecture is somewhat cookie cutter for each erp (netsuite will be one of those erps), i'm also thinking that setting up a linked server thru which this or CData's api is called disguised to look much like any other data source is also an option. if i'm not mistaken that would mean for this case adding CLR to sql if its not already there.
-
thx Brian and Phil. If Netsuite's invoice etc API's can do this, i would prefer to go that way. I show a snippet below of their api list etc. I dont see sufficient documentation there to tell me if i'd be going down a rabbit hole or not with their api's. All i want is invoice amts and dimensions from the last few days or a full list of the same depending on what my job indicates. using their soap capabilities sounds like a last (maybe second last) resort at this juncture.
They've been nice but I feel like i'm doing a dance with CData. They have 3 options none of which i've learned a price (even ballpark) on yet. odbc, jdbc or ssis component. and i dont even know if any one of those options can be purchased separately.
i have call auth to oracle but the "dept" menu list didnt seem to include a dept that can answer a getting started question about their api's. And unfortunately i missed their return call today because i think they silence their calls.
presumably , the link shown in the 1st image is what you call from ssis, and the specific api is more or less a parameter of sorts with a payload of parameters. in my case the api shown in the 2nd image is what i'm (i think) interested in. i'm guessing after i test in the sandbox, that the account id had better be some sort of non expiring account for just this purpose. while i can log into netsuite the only type of credentials i've seen so far are my own and who knows how a pswd would be attached in an ssis to netsuite api call.
-
You've got some fun R&D lined up.
This won't be a password thing. Instead you'll be using OAUTH2 to gain access and messing about with ClientIds, ClientSecrets and Access Tokens, I would expect.
Once you've got to grips with that, you can use the documentation to submit 'GETs' and then write code to strip out the bits you are interested in from the JSON responses.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
appreciate the support phil. if anyone out there ends up in my shoes it sounds like the "Suite Scripts" team is the one you want for guidance. I chose accounting just to get in the door because i had no idea.
From what i'm reading so far, a c# (or other .net language) "data source" script is the best choice of "bridge" you can build to do this (api) from ssis.
I'm quickly looking up forums i can join. This is going to be quite an adventure. i didnt see product in the list of returned values but i wasnt looking too closely. i did however see ship to and bill to info so i'm hopeful. i saw a record limit that i believe we can set. i think its default is 1,000 which probably wont be good enough for us.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply