Use API to populate SQL Server DB

Question

I am relatively new to this so if this is a dumb question please just link me to some relevant information.

We work with a third party vendor that recently opened up their databases using an API. Information on it can be found here: https://www.entrata.com/api-documentation. It returns the data in either an XML or JSON format as per usual.

We would like to use this API to copy relevant data onto our own SQL server so we can preform our own reporting (their provided reports aren't quite up to snuff with what we are looking for). We want this to be done regularly so I am really looking for a repeatable process. I have been searching around and I believe I am going to have to build a .NET application that GETs the data then parses it into a SQL friendly format for BULK INSERT, but I'm not sure if this is indeed the case or where to really start if it is.

Any information you can provide or even a point in the right direction would be greatly appreciated. Thank you for your time and effort.

P.S. At the moment I do not have access to our SQL server. I have been trying to get a logon from our IT department for the last week but they seem to be dragging their feet.

Are you sure the vendor isn't willing to just send you a nightly/weekly/whatever copy of the database? — itsme86
– itsme86, Commented Feb 15, 2017 at 15:50
Sounds perfectly doable - practice to sql developer on your pc — BugFinder
– BugFinder, Commented Feb 15, 2017 at 15:51
You could save the XML files to a folder and do an INSERT OR UPDATE query on your tables. You can read from them using OPENROWSET in a SQL query. Really the only programming you would have to do is getting the XML files, I believe. Get them, run the SQL job reading from the XML using OPENROWSET and then INSERT OR UPDATE. There also could be a way to get the XML directly from a http://....xml file from their API. I don't know enough about that to comment. — justiceorjustus
– justiceorjustus, Commented Feb 15, 2017 at 15:55

TrevorBrooks · Accepted Answer · 2017-02-15 15:57:18Z

1

Use SQL Server Integration Services (SSIS) to develop a package that imports the data. https://msdn.microsoft.com/en-us/library/ms169917.aspx

answered Feb 15, 2017 at 15:57

TrevorBrooks

3,9504 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use API to populate SQL Server DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related