c# - C# - 在 SSIS 脚本任务中反序列化 JSON

Question

我需要从 Web 服务读取 JSON 数据。

我在 SSIS 中创建了一个 C# 脚本任务来执行此操作。我能够连接到服务，进行身份验证并返回 Web 服务的结果。

JSON 的格式如下：

{
    "20": {
        "kpiData": [
            {
                "date": "2018-10-01",
                "interval": "DAILY",
                "kpiFigure": 80,
                "symbol": null,
                "additionalInfo": {}
            },
            {
                "date": "2018-10-02",
                "interval": "DAILY",
                "kpiFigure": 58,
                "symbol": null,
                "additionalInfo": {}
            }
        ],
        "average": 69,
        "weightedAverage": 0,
        "max": 80,
        "min": 58,
        "total": 138,
        "symbol": ""
    },
    "24": {
        "kpiData": [
            {
                "date": "2018-10-01",
                "interval": "DAILY",
                "kpiFigure": 133,
                "symbol": null,
                "additionalInfo": {}
            },
            {
                "date": "2018-10-02",
                "interval": "DAILY",
                "kpiFigure": 130,
                "symbol": null,
                "additionalInfo": {}
            }
        ],
        "average": 132,
        "weightedAverage": 0,
        "max": 133,
        "min": 130,
        "total": 263,
        "symbol": ""
    }
}

如何在 C# 脚本任务中反序列化它？

我需要让它以这样的“扁平”结构输出缓冲区：

SiteID ¦ date       ¦ interval ¦ kpiFigure
20     ¦ 2018-10-01 ¦ DAILY    ¦ 80
...

标签： c#jsonssis

Answer 1

这不是 C# 路线，根据我的评论和您的回复，您可能想探索在 T-SQL 中执行此操作。如果您以前没有，这里有一个高级示例，说明如何将其连接到 SSIS。只要你是 SQL Server 2016+ 版本

1. 在 SSIS 中有一个变量，我们只需调用 JsonResponse，Type=String
2. 您已经有了调用 Web 服务的 C# 脚本任务，将 JsonResponse 变量添加为 ReadWriteVariables

3. 修改了您的脚本任务并将响应写入该变量，我通常将响应流式传输到单独的变量，然后将其分配给我的 ReadWriteVariables：

           using (var response = (HttpWebResponse)request.GetResponse())
           {
               using (Stream responseStream = response.GetResponseStream())
               {
                   using (StreamReader streamReader = new StreamReader(responseStream))
                   {
                       responseString = streamReader.ReadToEnd();
                   }
                   Dts.Variables["User::JsonResponse"].Value = responseString;
               }
           }
   

4. 在控制流中的脚本任务之后，添加一个执行 SQL 任务

5. 在这里，您将拥有一个您调用的存储过程，该过程具有 @JsonResponse NVARCHAR(MAX) 参数。
6. 您将把上面的 JsonResponse 传递给这个存储过程，然后使用该过程，您可以使用 OPENJSON 解析该 JSON 并按照您的意愿使用它。插入另一个表等...

这是 T-SQL 关于如何解析您提供的示例的示例。您显然必须进行必要的调整才能将其转换为存储过程，并根据您的具体目标进行相应调整：

DECLARE @JsonResponse NVARCHAR(MAX);

SET @JsonResponse = N'
{
    "20": {
        "kpiData": [
            {
                "date": "2018-10-01",
                "interval": "DAILY",
                "kpiFigure": 80,
                "symbol": null,
                "additionalInfo": {}
            },
            {
                "date": "2018-10-02",
                "interval": "DAILY",
                "kpiFigure": 58,
                "symbol": null,
                "additionalInfo": {}
            }
        ],
        "average": 69,
        "weightedAverage": 0,
        "max": 80,
        "min": 58,
        "total": 138,
        "symbol": ""
    },
    "24": {
        "kpiData": [
            {
                "date": "2018-10-01",
                "interval": "DAILY",
                "kpiFigure": 133,
                "symbol": null,
                "additionalInfo": {}
            },
            {
                "date": "2018-10-02",
                "interval": "DAILY",
                "kpiFigure": 130,
                "symbol": null,
                "additionalInfo": {}
            }
        ],
        "average": 132,
        "weightedAverage": 0,
        "max": 133,
        "min": 130,
        "total": 263,
        "symbol": ""
    }
}
';

SELECT [a].[Key] AS [SiteID]
     , [b].*
FROM   OPENJSON(@JsonResponse) [a]
CROSS APPLY OPENJSON([a].[Value], '$.kpiData')
           WITH (
                    [date] DATE '$.date'
                  , [interval] NVARCHAR(100) '$.interval'
                  , [kpiFigure] INT '$.kpiFigure'
                ) [b];

只是一个选项/示例，说明如何将 JSON 传递到 T-SQL 并从那里解析它。