4

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Data Dill
  • 353
  • 4
  • 14

2 Answers2

4

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:

  1. Using Third Party component (this part)
  2. 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:

  1. SSIS JSON Source (File, REST API, OData)
  2. SSIS REST API Web Service Task

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:

(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:

(6) Read directly from POWER BI

If your end goal is to read REST API output in Power BI without the need of storing the data then try to do it without needing python or SSIS:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I do not believe Power BI will work because I have 500+ bitly links I need to request from, which is why I used Python so I could iterate through each of them and return the proper response. – Data Dill Feb 20 '19 at 00:57
  • 1
    @DylanJanszen then you should use one of the other approaches – Hadi Feb 20 '19 at 01:08
  • You can do it in two steps. 1. Run your py script and store the json output into a secure location. Then (2 step) Create an SSIS package that will read this json file to load unto your target database. – fo2bug Mar 15 '19 at 06:47
  • @fo2bug if it is a different method from the mentioned above. Why not writing an answer. It will be more helpful – Hadi Mar 15 '19 at 06:59
  • @fo2bug i think this is similar to section (4) – Hadi Mar 15 '19 at 07:00
  • 1
    @Hadi, yes it is similar to section(4). – fo2bug Mar 17 '19 at 22:45
  • @fo2bug anyway i think that the OP ignored the whole topic :) – Hadi Mar 17 '19 at 22:48
1

Use a site like this to help you since you are starting from scratch...

https://jsonutils.com/

This site will help you create classes and tables from your JSON text.

From that point it is really simple to use a script task to pull the data and push it into tables. Here is a simple deserialization code that you will need to customize.

System.Net.WebClient wc = new System.Net.WebClient();

        RootObject ro=null;

        try
        {
            string resp = wc.DownloadString("https://restcountries.eu/rest/v2/alpha/" + Row.Code.ToString());

            JavaScriptSerializer js = new JavaScriptSerializer();

            ro = js.Deserialize<RootObject>(resp);
        }
        catch (Exception e)
        { }

        if (ro != null)
        {
            Row.Region = ro.Region;
            Row.SubRegion = ro.Subregion;
        }

You'll need to add these namespaces

using System.Net;
using System.Collections.Generic;
using System.Web.Script.Serialization;
KeithL
  • 5,348
  • 3
  • 19
  • 25