sql - 使用来自 MS SQL 的 API 检索表的简单方法

Question

我是API的菜鸟，所以请温柔。

我在网上找到了一段代码，我想用它来将数据检索到我的 SQL Server 2019 上的表中。

当我运行它时，它什么也不返回，但是如果我将 API URL 粘贴到我的浏览器中，它会返回数据。

该服务器似乎没有为我提供“WSDL”来使用集成服务。

我怎样才能让它工作？该文档对于在 SQL 中实现这一点并没有太大帮助。

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                 'https://api.nzbplanet.net/api?&t=search&q=wing&apikey={{myapikey}}', --Your Web Service Url (invoked)
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object

标签： sqlsql-serverapissiswsdl

Answer 1

您可以使用 SSIS 脚本组件来检索 API 的负载并将结果写入输出缓冲区。然后将结果写入 SSIS。出于安全考虑，我会避免将数据库直接连接到互联网。

下面是一个例子：

1) 这使用 webclient 类连接到 api。在这种情况下，使用令牌来授权连接 (authToken)。它还使用 TLS 1.2，您的连接可能不需要它

2）Uri存储在ssis中的一个变量中，然后在scriptcomponent中指定为只读变量

3) 在这种情况下，DownloadString() 方法会下载一个 json 有效负载。Newtonsoft.Json 用于反序列化 json。json 在下面也被定义为类，因此可以将其解析为 .NET 对象

4) outputbuffer 列在脚本组件中定义。我们遍历数据类中的每个值并为每一行设置值。

5) 最后一步只是将此脚本组件源映射到 sql 数据库的 oledb 目标。

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Newtonsoft.Json;
using System.Collections.Generic;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void CreateNewOutputRows()
    {
        var uri = Variables.Uri;
        string authToken = Variables.authToken;
        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

        using (var client = new WebClient())
        {
            client.Headers.Add(HttpRequestHeader.ContentType, "application/json");
            client.Headers.Add("authToken", authToken);
            var response = client.DownloadString(uri);
            json parsedResponse = JsonConvert.DeserializeObject<json>(response);
            foreach (var value in parsedResponse.data.values)
            {
                Output0Buffer.AddRow();
                Output0Buffer.CreatedBy = value.CreatedBy;
                Output0Buffer.CreatedOn = value.CreatedOn;
            }
        }
    }
}
public class json
{
    public status responseStatus { get; set; }
    public data data { get; set; }
}

public class data
{
    public List<values> values { get; set; }
}

public class values
{

    public string CreatedOn { get; set; }
    public string CreatedBy { get; set; }
}