0

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; }
    }
}

1 Answer 1

1

You have:

OutputBuffer.responsesdisplaykey = string.Join(",", CER.responses.display_key);

Based on the class definitions, CER.responses is a List<Respons>. If you want a comma-separated list of display keys, you'll need to project the Respons objects to strings.

OutputBuffer.responsesdisplaykey = string.Join(",", CER.responses.Select(r=>r.display_key));
Sign up to request clarification or add additional context in comments.

4 Comments

Hi Chris thank you for your reply. I am getting the following error message:Error 1 'System.Collections.Generic.List<ScriptMain.Respons>' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type 'System.Collections.Generic.List<ScriptMain.Respons>' could be found (are you missing a using directive or an assembly reference?)
I have added using System.Linc I am getting the display_key ... any advice on how I can get the valaues for "answer" as well please? Thank you for your help :)
please ignore my last comment. My lack of confidence in C-sharp scripting. I have got it working: OutputBuffer.responsesdisplaykey = string.Join(",", CER.responses.Select(r => r.display_key + ":" + r.answer));
Sorry for not checking in sooner. I'm glad you've got it worked out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.