如何创建从Python REST API请求到MSSQL服务器的ETL JSON的SSIS包? [英] How to create a SSIS package to ETL JSON from Python REST API request into MSSQL server?

问题描述

我正在尝试获取我编写的Python脚本，该脚本使用REST API发出GET请求，并以JSON形式返回数据，然后将数据插入到我必须创建的SQL服务器中. /p>

这项工作每天至少需要运行一次.我不熟悉在MSSQL中创建表，更不用说创建SSIS包或使用ETL了.

对于没有实际经验，但从概念上很好地理解该过程本身的人来说，我希望能获得一些指导，以了解如何做到这一点以及它的现实性.

我的最终目标是将这些数据导入Power BI文件中以进行实时报告.

解决方案

我认为您不会得到非常详细的答案，因为您没有显示已完成的任何试用，并且您似乎需要从头开始回答.我要做的是提及一些可用于实现目标的方法，并且我将为每种方法提供一些链接以获取有关其的更多信息.

(1)为什么选择SSIS?

我不完全理解您对

的意思.

我正在尝试使用我编写的Python脚本，该脚本使用REST API进行GET请求，并以JSON的形式返回数据，然后将数据插入到我必须创建的MSSQL服务器中.

如果成功创建了Python脚本，而问题仅在于每天运行它.那么，为什么不使用操作系统调度程序来执行此脚本呢?如果脚本尚未创建，并且仅从Rest Api读取，并将其打印为输出. 然后忽略此部分 .

(2)使用第三方组件

如果您决定使用SSIS，我认为没有必要使用python脚本，因为它尚未集成，并且可能需要其他工作.

在SSIS中，没有用于Rest API或JSON源的特定组件，您有两种选择:

1. 使用第三方组件(本部分)
2. 使用脚本组件(下一部分)

您可以参考Visual Studio市场中各种各样的第三方组件.作为示例， ZappySys 提供了许多组件，例如:

1. SSIS JSON源(文件，REST API，OData)
2. SSIS REST API Web服务任务

他们发表了一些有关如何使用这些组件的指南:

• 如何加载JSON /REST API到SSIS中的SQL Server
• SSIS JSON源(文件，REST API，OData)

(3)使用脚本组件

您可以编写脚本组件来从Rest API获取数据并将其导入到SQL Server，而不是使用第三方组件.您可以参考以下链接中的打开以获取更多信息:

• 在SSIS中使用REST API提取前100条用户推文
• 使用以下命令下载JSON数据SSIS
• 通过SSIS使用Rest Api
• 使用JSON在SSIS中将Feed作为数据源

(4)使用执行处理任务执行Python脚本

另一个建议是从使用SSIS抓取数据和Python

(5)使用IronPython

IronPython 是Python编程语言的开源实现，该语言与.NET Framework紧密集成. IronPython可以使用.NET Framework和Python库，而其他.NET语言也可以轻松使用Python代码.

我以前没有使用过这个库，也不知道我是否可以提供帮助.昨天，我读了评论 @billinkc 链接到下面的答案，其中包含有关如何执行此操作的出色指南:

• SSIS:通过SSIS执行Ironpython或Ironruby脚本

(6)直接从POWER BI读取

如果您的最终目标是在Power BI中读取REST API输出而不需要存储数据，则尝试在不需要python或SSIS的情况下进行操作:

• 使用电源具有JSON数据源和文件的BI
• 如何在Power BI中导入REST API(加载JSON/SOAP XML) (使用第三方组件)

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.

解决方案

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:

• How to load JSON / REST API to SQL Server in SSIS
• SSIS JSON Source (File, REST API, OData)

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

• Using REST API in SSIS to Extract Top 100 User Tweets
• Download JSON Data with SSIS
• Consume Rest Api with SSIS
• Using a JSON Feed as a Data Source in SSIS

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

• SSIS: Execute Ironpython or Ironruby scripts through SSIS

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

• How to call REST APIs and parse JSON with Power BI
• Using Power BI with JSON Data Sources and Files
• How to import REST API in Power BI (Load JSON / SOAP XML) (Using third party component)

这篇关于如何创建从Python REST API请求到MSSQL服务器的ETL JSON的SSIS包?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！