Get data or values from JSON

Question

I am creating a solution in SSIS to get data from an API/url, example below: URL is obviously is different in actual project

https://{your_server}}/api/v1/{your_account}/internalqueries?queryguid=F6AC1BB3-DC03-4E64-BC46-30DEDE3BD27E&filterguid=AAED4F63-76CC-4E09-8C97-2C425D4E48EF&viewguid=DD4F7938-951F-4279-953B-A6EB075FCB35&max_rows=3

Expected JSON Result:

{
    "HREF": "https://{your_server}/api/v1/{your_account}/internalqueries?queryguid=%7B6938436D-B024-4B9E-9815-A41C1D7C7A0E%7D&filterguid=%7B2FAC2998-B0EC-45BF-9B98-1A09B1F8C343%7D&viewguid=%7B631587D5-EED1-49C7-9252-54E08A398CDE%7D&max_rows=3",
    "recordcount": 3,
    "previouspage": 0,
    "nextpage": 0,
    "records": [
        {
            "Creation Date": "1/9/2017 11:07:34 am",
            "Incident Number": "I181218_000003",
            "Category": "Get Help",
            "Title": "",
            "Recipient": "William",
            "Description": "",
            "Manager": "",
            "Status": "In Progress",
            "Time Status": "4",
            "Priority": "2",
            "SLA Target": "1/11/2017 11:07:34 am",
            "Cost": "70.00",
            "End Date": ""
        },
        
    ]
}

Created a class called ResultAPI:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SC_b15eeeff0e3544d3a882578b3eb9bbba
{
    public class ResultAPI
    {
        public string HREF { get; set; }
        public int recordcount { get; set; }
        public int previouspage { get; set; }
        public string Recipient { get; set }
        public string records { get; set; }
    }
}

Another class called GenericResult:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SC_b15eeeff0e3544d3a882578b3eb9bbba
{
    public class GenericResponse

    {
        public ResultAPI[] ListData { get; set; }
    }
    public class ResultGen
    {
        public GenericResponse Result { get; set; }
    }
}

Main:

public override void CreateNewOutputRows()
{

    ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
    HttpClient client = new HttpClient();
        client.BaseAddress = new Uri("https://{your_server}}/api/v1/{your_account}/internalqueries?queryguid=F6AC1BB3-DC03-4E64-BC46-30DEDE3BD27E&filterguid=AAED4F63-76CC-4E09-8C97-2C425D4E48EF&viewguid=DD4F7938-951F-4279-953B-A6EB075FCB35&max_rows=3");
        client.DefaultRequestHeaders.Accept.Clear();    
        client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));

    var cred = Convert.ToBase64String(Encoding.ASCII.GetBytes(string.Format("{0}:{1}", "username", "Password")));
    client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Basic", cred);

    string APIUrl = ("https://{your_server}}/api/v1/{your_account}/internalqueries?queryguid=F6AC1BB3-DC03-4E64-BC46-30DEDE3BD27E&filterguid=AAED4F63-76CC-4E09-8C97-2C425D4E48EF&viewguid=DD4F7938-951F-4279-953B-A6EB075FCB35&max_rows=3");


    var response = client.GetAsync(APIUrl).Result;
        if (response.IsSuccessStatusCode)
        {
            var result = response.Content.ReadAsStringAsync().Result;
            var serializer = new JavaScriptSerializer();
            serializer.MaxJsonLength = Int32.MaxValue;
            var data = serializer.Deserialize<ResultAPI>(result);

        APIResultsBuffer.AddRow();
        APIResultsBuffer.previouspage = data.previouspage;
        APIResultsBuffer.recordcount = data.recordcount;
        APIResultsBuffer.HREF = data.HREF;
       
        // Getting error here Property of indexer cannot be assigned to---it is read only**
       APIResultsBuffer.records = data.records;
       // this is just for illustration if I want to get actual values of the Recipient field in the SQL table**
       // *APIResultsBuffer.Recipient= data.Recipient*
        
    }
    
    }
    
}

I have SQL Server DB as an OLE Destination, what I am trying to figure out is how do I get actual records in SQL Server table, all I am getting is HREF, previouspage and recordcount correctly and everything else is blank but what should be the data type for records in Script Component--Input and Output and what changes I need to make in order to get the actual Recipient data from the JSON above.

Any help appreciated.

Tried changing data type for records but nothing seems to be working

To update: I updated the CreatedNewOutput section:

JavaScriptSerializer serializer = new JavaScriptSerializer();
serializer.MaxJsonLength = Int32.MaxValue; 
List<ResultAPI> suppliers = serializer.Deserialize<List<ResultAPI>>(APIUrl); 
foreach (ResultAPI resultAPI in suppliers) 
    APIResultsBuffer.HREF = resultAPI.Recipient;

I am getting an error: Invalid JSON primitive: https.

Answer 1

To address

// Getting error here Property of indexer cannot be assigned to---it is read only**
APIResultsBuffer.records = data.records;

I'm guessing you defined the records column in SSIS as DT_TEXT or DT_NTEXT which is why you're getting that error. You cannot directly assign a value to a blob type in SSIS. Instead, you need to call the AddBlobData but you can only pass in a byte array so you'll need to crib from How do I convert a String to a BlobColumn in SSIS

    Output0Buffer.AddRow();
    string aString = "X".PadRight(10000) + "X";
    // https://stackoverflow.com/questions/35703025/how-do-i-convert-a-string-to-a-blobcolumn-in-ssis
    byte[] bytes = new byte[aString.Length * sizeof(char)];
    System.Buffer.BlockCopy(aString.ToCharArray(), 0, bytes, 0, bytes.Length);
    Output0Buffer.RecordsText.AddBlobData(bytes);