在 SSIS 中使用 C# 从 JSON API 读取 [英] Reading from JSON API with C# in SSIS

问题描述

我想从 SSIS 中的 JSON API 读取一些数据并将其写入 SQL Server 中的表中.我已经使用 3rd 方解决了这个任务，但解决方案不是那么优雅，所以现在我尝试使用 SSIS 的脚本组件在 Visual Studio 中自己编写脚本.

我在网上研究了解决方案，并以这个结果结束.到目前为止，我对正在发生的事情相当有信心，但我缺乏最终的方向.我知道我需要以某种方式将输出映射到我在 SSIS 中创建的列.我想我必须围绕 CreateNewOutputRows() 做些事情，但我不确定是什么.有人可以帮我解决这个问题吗?此外，由于这或多或少是我的第一个 c# 脚本，如果有更简单的解决方案或在某些方面不合适等，我也将不胜感激.

首先，API 的输出看起来像这样

代码在 CreateNewOutPutRows() 中.我没有 Newtonsoft，只是在这里使用 JavaScriptSerializer 来显示一个工作示例，以便您了解如何连接它:

使用系统；使用 System.Data;使用 Microsoft.SqlServer.Dts.Pipeline.Wrapper;使用 Microsoft.SqlServer.Dts.Runtime.Wrapper；使用 System.Web.Script.Serialization；使用 System.Collections.Generic;[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]公共类 ScriptMain : UserComponent{公共覆盖无效 CreateNewOutputRows(){string json = @"{""data"":[{""created_at"":""2016-03-12 09:45:00"",""created_at_unix"":1457772300,""shop_name"":""DK - 一些名字"",""location_id"":1111,""custom_location_id"":""2222"",""custom_shop_id"":""2222"",""shop_id"":3333,""count_in"":""1"",""count_out"":""1"",""timekey"":3},{""created_at""":""2016-03-12 09:45:00"",""created_at_unix"":1457772300,""shop_name"":""test2"",""location_id"":1111,""custom_location_id"":""2222"",""custom_shop_id"":""2222"",""shop_id"":3333,""count_in"":""1"",""count_out"":""1"",""timekey"":3}]}";RootObject Test = new JavaScriptSerializer().Deserialize(json);/** 这是将数据添加到输出缓冲区的地方.* 在 AddRow() 之后，您基本上将手动添加的列(左侧)映射到数据(右侧).* 使用 foreach 循环遍历反序列化 json*/foreach(Test.data 中的 var 项目){Output0Buffer.AddRow();Output0Buffer.createdat = item.created_at;Output0Buffer.shopname = item.shop_name;}}公共类 RootObject{公共列表<数据>数据{得到;放;}}公开课数据{公共字符串 created_at { 获取；放;}公共 int created_at_unix { 获取；放;}公共字符串 shop_name { 获取；放;}公共 int location_id { 获取；放;}公共字符串 custom_location_id { 获取；放;}公共字符串 custom_shop_id { 获取；放;}公共 int shop_id { 获取；放;}公共字符串 count_in { 获取；放;}公共字符串count_out { 获取；放;}公共 int timekey { 获取；放;}}}

然后在此示例中，只需使用记录集目标并启用数据查看器，您就可以看到返回的各个行:

I want to read some data from a JSON API in SSIS and write it to a table in SQL Server. I've solved the task using a 3rd party, but the solution isn't that elegant, so now I'm trying to script it myself in Visual Studio using SSIS's script component.

I've researched around the web for solutions, and ended with this result. So far, I'm fairly confident about what is going on, but I lack the final direction for this. I know I need to somehow map the output to the columns I've created in SSIS. I guess I have to do something around CreateNewOutputRows(), but I'm not sure what. Can someone please help me out on this? Also, since this is more or less my first ever c# script, I would also appreciate it, if there's a way easier solution OR if it is in some way inappropriate etc.

First of all, the output from the API looks like this (API documentation here):

 "data":[  
      {  
         "created_at":"2016-03-12 09:45:00",
         "created_at_unix":1457772300,
         "shop_name":"DK - Some name",
         "location_id":1111,
         "custom_location_id":"2222",
         "custom_shop_id":"2222",
         "shop_id":3333,
         "count_in":"1",
         "count_out":"1",
         "timekey":3
      }

The script I've got so far is

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


[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public static void Main()
    {
        //Test api
        var url = "https://login.vemcount.com/api/fetch_data/?data={"api_key":"xxxxxxx","type":"company_id","id":"10","date_from":"2019-01-01 00:00","interval":"15min", "group_by":"shop_id"}";
        var json = new WebClient().DownloadString(url);

        var root = JsonConvert.DeserializeObject<RootObject>(json);

        //Printing last record
        Console.WriteLine(root.data.Last().created_at);
    }
    public class data
    {

        public string created_at { get; set; }
        public int created_at_unix { get; set; }
        public string shop_name { get; set; }
        public int location_id { get; set; }
        public string custom_location_id { get; set; }
        public string custom_shop_id { get; set; }
        public int shop_id { get; set; }
        public string count_in { get; set; }
        public string count_out { get; set; }
        public int timekey { get; set; }
    }

    public class RootObject
    {
        public List<data> data { get; set; }
    }
    public override void CreateNewOutputRows()
    {

    }

}

解决方案

You'll put all that code in CreateNewOutputRows().

First you have to manually add all the columns to the script component. In the example here I only added 2 columns:

The code goes in CreateNewOutPutRows(). I don't have Newtonsoft, just using the JavaScriptSerializer here to show a working example so you can see how to hook it up:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization;
using System.Collections.Generic;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        string json = @"{""data"":[{""created_at"":""2016-03-12 09:45:00"",""created_at_unix"":1457772300,""shop_name"":""DK - Some name"",""location_id"":1111,""custom_location_id"":""2222"",""custom_shop_id"":""2222"",""shop_id"":3333,""count_in"":""1"",""count_out"":""1"",""timekey"":3},{""created_at"":""2016-03-12 09:45:00"",""created_at_unix"":1457772300,""shop_name"":""test2"",""location_id"":1111,""custom_location_id"":""2222"",""custom_shop_id"":""2222"",""shop_id"":3333,""count_in"":""1"",""count_out"":""1"",""timekey"":3}]}";

        RootObject Test = new JavaScriptSerializer().Deserialize<RootObject>(json);

        /*
         * This is where data gets added to the output buffer.
         * After AddRow() you are basically mapping the column you manually added(on the left) to the data(on the right).
         * using a foreach loop to loop through the deserialize json
         */
        foreach (var item in Test.data)
        {
            Output0Buffer.AddRow();
            Output0Buffer.createdat = item.created_at;
            Output0Buffer.shopname = item.shop_name;
        }
    }
    public class RootObject
    {
        public List<data> data { get; set; }
    }

    public class data
    {

        public string created_at { get; set; }
        public int created_at_unix { get; set; }
        public string shop_name { get; set; }
        public int location_id { get; set; }
        public string custom_location_id { get; set; }
        public string custom_shop_id { get; set; }
        public int shop_id { get; set; }
        public string count_in { get; set; }
        public string count_out { get; set; }
        public int timekey { get; set; }
    }

}

Then in this example, just using a record set destination and enabled data viewer so you can see the individuals rows come back out:

这篇关于在 SSIS 中使用 C# 从 JSON API 读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！