SSIS - Import data from python Rest API into SQL Server

- April 15, 2019

Question:

I am trying to take a Python script I wrote that makes GET requests utilizing a REST API and returns data in the form of JSON and then have that data be inserted into a SQL server that I will have to create.

This job will need to run each day at least once. I am not familiar with creating tables in MSSQL let alone creating a SSIS package or working with ETL.

I would appreciate some direction as to how to do this and how realistic it is for somebody with little actual experience, but a good understanding of the process itself conceptually.

My end goal is to import this data into a Power BI file for live reporting.

Answer:

I don't think you will get a very detailed answer, since you are not showing any trial you have done and it looks like you need an answer from scratch. What i will do is to mention some of the approaches you can use to achieve your goal and i will provide some links for each approach to get more information on it

(1) Why SSIS?

I didn't understood exactly what you meant with

I am trying to take a Python script I wrote that makes GET requests utilizing a REST API and returns data in the form of JSON and then have that data be inserted into a MSSQL server that I will have to create.

If the Python Script is created successfully and the problem is only in running it in a daily basis. Then why not using the Operating System Scheduler to Execute this script? If the Script is not created yet and it only read from a Rest Api and print it as output. then ignore this part.

(2) Using Third Party Component

I don't think there is a need to use the python script if you decide to go with SSIS since is not integrated and it may requires additional work to do.

In SSIS there is no specific component for Rest API or JSON source, you have two choices:

Using Third Party component (this part)
Using a Script Component (next part)

There are a wide variety of third party component in the Visual Studio marketplace that you can refer to. As Example ZappySys has provided many component such as:

And they published some guide on how to use these components:

(3) Using a Script Component

Instead of using a third party component, you can write a script component that Get the data from Rest API and import it to SQL Server. You can refer to on of the following links for more information:

(4) Executing Python Script using Execute Process Task

Another suggestion is to execute the python script from an Execute Process Task to a Flat File then read from the flat file to SQL Server, you can refer to the following link for more information:

Scraping data with SSIS and Python

(5) Using IronPython

IronPython is an open-source implementation of the Python programming language which is tightly integrated with the .NET Framework. IronPython can use the .NET Framework and Python libraries, and other .NET languages can use Python code just as easily.

I didn't used this library before and i don't know if i can help. Yesterday i have read a comment wrote by @billinkc linking to the answer below which contains an amazing guide on how to do that: