Any help would be appreciated. I am trying to parse data from a JSON source file in SSIS (SQL Server Integration Services). I can parse through the data but am getting stuck on parsing the data where there is a 'one to many' relationship. There is a data entity repeated several times ("display_k") -
"responses":
[
{"display_k":"good","answer":null}
,{"display_k":"bad","answer":null}
,{"display_k":"general","answer":"The whole process was Easy. "}
,{"display_k":"would_buy_again","answer":true}
,{"display_k":"happy_with_customer_service","answer":null}
]
The full code is below:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
//using Newtonsoft.Json;
using System.Collections.Generic;
using System.Runtime.Serialization.Json;
using System.Text;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void CreateNewOutputRows()
{
var filePath = Connections.Connection.AcquireConnection(null).ToString();
using (var fileContents = new StreamReader(filePath))
while (fileContents.Peek() >= 0)
{
var record = fileContents.ReadLine();
var ser = new DataContractJsonSerializer(typeof(RootObject));
var memStream = new MemoryStream(UTF8Encoding.UTF8.GetBytes(record));
var root = ser.ReadObject(memStream) as RootObject;
//reviewables
var CustomerExperienceReview = root.customer_experience_reviews;
foreach (var CER in CustomerExperienceReview)
{
OutputBuffer.AddRow();
OutputBuffer.id = CER.id;
OutputBuffer.branchattribution = CER.branch_attribution;
OutputBuffer.reviewerfirstname = CER.reviewer.first_name;
OutputBuffer.reviewerid = CER.reviewer.id;
// Cannot get the output buffer to show the correct result:
OutputBuffer.responsesdisplaykey = string.Join(",", CER.responses.display_key);
}
}
}
public class Pagination
{
public int total_entries { get; set; }
public int current_page { get; set; }
public int total_pages { get; set; }
public int per_page { get; set; }
public object previous_page { get; set; }
public int next_page { get; set; }
}
public class Summary
{
public Pagination pagination { get; set; }
public int moving_window_size { get; set; }
public SortOrder sort_order { get; set; }
public List<object> sort_orders { get; set; }
}
public class Reviewer
{
public string first_name { get; set; }
public int id { get; set; }
}
public class Respons
{
public string display_key { get; set; }
public object answer { get; set; }
}
public class CustomerExperienceReview
{
public int id { get; set; }
public string branch_attribution { get; set; }
public List<Respons> responses { get; set; }
}
public class RootObject
{
public Summary summary { get; set; }
public List<CustomerExperienceReview> customer_experience_reviews { get; set; }
}
}