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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply