SSIS - Import data from python Rest API into SQL Server

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

Comments

  1. James ZicrovJuly 24, 2019 at 3:08 PM

    I think both REST API and SQL can provide some good solutions to complex IT problems.

    SQL Server Load Soap Api

    ReplyDelete

Post a Comment

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

-
Question: When i execute the SSIS package the Script Task throws the following exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Which is meaningless. How can i read the real error message? Answer: Exception has been thrown by the target of an invocation. Is an general exception that is thrown by Script T...
Read more

Don’t install Hadoop on Windows!

-
Image
A few years ago, I was hearing from my colleagues “don’t ever think about installing Hadoop on Windows operating system!” . I was not convinced of this saying because I am a big fan of Microsoft products, especially Windows. In the past three years, I worked on three projects where I was asked to build a Hadoop cluster on Ubuntu. The first time, it was a single-node Hadoop installation with a single Apache Spark worker. The other projects were about building a Big Data ecosystem for radiation data engineering , where the multi-node Hadoop cluster is deployed. Besides Hadoop, we installed and configured Apache Kafka, Spark, Hive, Pig, and Flume installations (I have published some installation guides previously, you can check the links at the end of this article). It was hard to become familiar with those technologies for the first time since they don’t have much documentation online. Each time I had to install Hadoop, I was thinking that why always Hadoop is installed on Linu...
Read more

SSIS - script component DateTime Formats Implicit Conversion

-
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin...
Read more