May 9, 2016 at 8:00 pm
Hi All, I am trying to see if I can get guidance on using SSIS JSON connector to use API from a website to access their data and to schedule Job on everyday basis.
Any help will be appreciated.
May 10, 2016 at 7:10 am
As no one has answered this in regards to SSIS specific ways, here is what I do.
When I want to pull data via a third-party API service using HTTP/HTTPS, I typically do this using Python. It allows me to access the data within a few lines of code to both authentic through basic authentication (user/pass) and reading JSON. Once I can start pulling data, I can then use the JSON modules in Python to read JSON and convert it to CSV using another module in Python that allows me to save anything to CSV.
Taking this approach allows me to create a lightweight Python script that allows me to connect, query JSON data, convert it to a CSV file and absorb it into SQL Server using SSIS. The execution of the script can be independent in a separate environment like an APP server or have SSIS or SQL execute it directly.
If you're not too familiar with Python, do not fret, it's extremely easy and there are many guides out there that show you how to connect to API's along with using JSON and CSV modules.
May 10, 2016 at 5:48 pm
Thank you for your reply. So using Json source connector is not easy way of doing it ?
I will try to use phython or I can use visual studio project to read data from json and dump that in SQL and create a package from there.
it might be a bit of work around but will see.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply