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

问题描述

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

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.

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

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.

首先，API的输出如下所示:(此处是API文档):

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()
    {

    }

}

推荐答案

您将所有这些代码放入CreateNewOutputRows().

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

首先，您必须手动将所有列添加到脚本组件.在此处的示例中，我仅添加了2列:

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

代码在CreateNewOutPutRows()中.我没有Newtonsoft，只是在这里使用JavaScriptSerializer展示了一个有效的示例，因此您可以看到如何将其连接起来:

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屋！