Reading from JSON API with C# in SSIS

Question

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

    }

}

Answer 1

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: