Home > Mobile >  SSIS Http Get Request Json Deserialize
SSIS Http Get Request Json Deserialize

Time：02-23

I am attempting to retrieve data from a JotForm form using SSIS and it works only as far as getting a response, however the data is very messy and needs to be deserialzed. Below is a sample of the response (I put a breakpoint in the script and just copied the value into Notepad);

"{\"responseCode\":200,\"message\":\"success\",\"content\":[{\"id\":\"555555555555555555\",\"form_id\":\"2222222222222222\",\"ip\":\"195.195.176.147\",\"created_at\":\"2022-02-09 07:04:52\",\"status\":\"ACTIVE\",\"new\":\"1\",\"flag\":\"0\",\"notes\":\"\",\"updated_at\":null,\"answers\":{\"1\":{\"name\":\"standardForm\",\"order\":\"1\",\"text\":\"Standard Form Template UNIT-e\",\"type\":\"control_head\"},\"2\":{\"name\":\"submit2\",\"order\":\"20\",\"text\":\"Submit\",\"type\":\"control_button\"},\"3\":{\"name\":\"familyName\",\"order\":\"2\",\"text\":\"Family Name\",\"type\":\"control_textbox\",\"answer\":\"Michael\"},\"4\":{\"name\":\"givenName\",\"order\":\"3\",\"text\":\"Given Name\",\"type\":\"control_textbox\",\"answer\":\"Myers\"},\"6\":

Now, as you can see, it's pretty messy (for me, anyway) and what I am trying to retrieve is the givenName and the familyName. I have created a Script Component and below is the code in main.cs;

  public override void CreateNewOutputRows()
    {

        System.Net.ServicePointManager.SecurityProtocol =
            SecurityProtocolType.Tls12 |
            SecurityProtocolType.Tls11 |
            SecurityProtocolType.Tls;

        HttpClient client = new HttpClient();
        client.BaseAddress = new Uri("https://mycompany.jotform.com/API/form/222222222222222222/submissions&apiKey=25555555xxxxxxxxxxx5555555");

        client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));

        string APIUrl = string.Format("https://mycompany.jotform.com/API/form/222222222222222222/submissions&apiKey=25555555xxxxxxxxxxx5555555");

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

            Output0Buffer.AddRow();
            Output0Buffer.FirstName = data.givenName;
            Output0Buffer.Surname = data.familyName;
           

            Console.WriteLine(result);
            System.Diagnostics.Debug.WriteLine(result);

            var json = JsonSerializer.Serialize(response);

            Console.WriteLine(json);
            System.Diagnostics.Debug.WriteLine(json);


        }
    }

I created two classes named Generic Response and ResultAPI like so (GenericResponse.cs);

namespace SC_98d911fb90f6449b86ed276a074f46c6
{
    public class GenericResponse
    {

        public ResultAPI[] ListData { get; set; }
    }


    public class ResultGen
    {
        public GenericResponse Result { get; set; }

    }

}

The ResultAPI Class looks like this;

namespace SC_98d911fb90f6449b86ed276a074f46c6
{
    public class ResultAPI
    {
        public string givenName { get; set; }
        public string familyName { get; set; }

    }
}

The response is supposed to be to be sent to a Flat File Destination (.txt) which I have mapped, but nothing is actually being sent.

I'd be more than grateful if you could please help me get to the bottom of this.

EDIT: as requested, the JSON response is as shown below (I have only posted the first part as it exceeds the allowed character limit);

"{\"responseCode\":200,\"message\":\"success\",\"content\":[{\"id\":\"555555555555555555\",\"form_id\":\"2222222222222222\",\"ip\":\"195.xxx.xxx.xxx\",\"created_at\":\"2022-02-09 07:04:52\",\"status\":\"ACTIVE\",\"new\":\"1\",\"flag\":\"0\",\"notes\":\"\",\"updated_at\":null,\"answers\":{\"1\":{\"name\":\"standardForm\",\"order\":\"1\",\"text\":\"Standard Form Template UNIT-e\",\"type\":\"control_head\"},\"2\":{\"name\":\"submit2\",\"order\":\"20\",\"text\":\"Submit\",\"type\":\"control_button\"},\"3\":{\"name\":\"familyName\",\"order\":\"2\",\"text\":\"Family Name\",\"type\":\"control_textbox\",\"answer\":\"Michael\"},\"4\":{\"name\":\"givenName\",\"order\":\"3\",\"text\":\"Given Name\",\"type\":\"control_textbox\",\"answer\":\"Myers\"},\"6\":{\"name\":\"title\",\"order\":\"4\",\"text\":\"Title\",\"type\":\"control_dropdown\",\"answer\":\"Dr\"},\"7\":{\"name\":\"gender\",\"order\":\"5\",\"text\":\"Gender\",\"type\":\"control_dropdown\",\"answer\":\"Male\"},\"8\":{\"name\":\"dateOf\",\"order\":\"6\",\"sublabels\":\"{\\\"day\\\":\\\"Day\\\",\\\"month\\\":\\\"Month\\\",\\\"year\\\":\\\"Year\\\",\\\"last\\\":\\\"Last Name\\\",\\\"hour\\\":\\\"Hour\\\",\\\"minutes\\\":\\\"Minutes\\\",\\\"litemode\\\":\\\"Date\\\"}\",\"text\":\"Date of Birth\",\"type\":\"control_datetime\",\"answer\":{\"day\":\"13\",\"month\":\"08\",\"year\":\"1963\"},\"prettyFormat\":\"13-08-1963\"},\"10\":{\"name\":\"nationality\",\"order\":\"7\",\"text\":\"Nationality\",\"type\":\"control_dropdown\",\"answer\":\"AMERICAN SAMOA [796\\/2]\"},\"11\":{\"name\":\"address\",\"order\":\"8\",\"sublabels\":\"{\\\"cc_firstName\\\":\\\"First Name\\\",\\\"cc_lastName\\\":\\\"Last Name\\\",\\\"cc_number\\\":\\\"Credit Card Number\\\",\\\"cc_ccv\\\":\\\"Security Code\\\",\\\"cc_exp_month\\\":\\\"Expiration Month\\\",\\\"cc_exp_year\\\":\\\"Expiration Year\\\",\\\"addr_line1\\\":\\\"Street Address\\\",\\\"addr_line2\\\":\\\"Street Address Line 2\\\",\\\"city\\\":\\\"City\\\",\\\"state\\\":\\\"State \\\\\\/ Province\\\",\\\"postal\\\":\\\"Postal \\\\\\/ Zip Code\\\",\\\"country\\\":\\\"Country\\\"}\",\"text\":\"Address\",\"type\":\"control_address\",\"answer\":{\"addr_line1\":\"17 Clover Avenue\",\"addr_line2\":\"Bethesda\",\"city\":\"Kent\",\"country\":\"United Kingdom\"},\"prettyFormat\":\"Street Address: 17 Clover Avenue<br>Street Address Line 2: Bethesda<br>City: Kent<br>Country: United Kingdom<br>\"},\"12\":{\"name\":\"emailAddress\",\"order\":\"10\",\"text\":\"Email Address\",\"type\":\"control_email\",\"answer\":\"[email protected]\"},\"13\":{\"name\":\"telephoneNumber\",\"order\":\"11\",\"sublabels\":\"{\\\"country\\\":\\\"Country Code\\\",\\\"area\\\":\\\"Area Code\\\",\\\"phone\\\":\\\"Phone Number\\\",\\\"full\\\":\\\"Phone Number\\\",\\\"masked\\\":\\\"Please enter a valid phone number.\\\"}\",\"text\":\"Telephone Number\",\"type\":\"control_phone\",\"answer\":{\"country\":\" 44\",\"area\":\"7888\",\"phone\":\"167989\"},\"prettyFormat\":\"( 44) (7888) 167989\"},\"14\":{\"name\":\"mobileTelephone\",\"order\":\"12\",\"sublabels\":\"{\\\"country\\\":\\\"Country Code\\\",\\\"area\\\":\\\"Area Code\\\",\\\"phone\\\":\\\"Phone Number\\\",\\\"full\\\":\\\"Phone Number\\\",\\\"masked\\\":\\\"Please enter a valid phone number.\\\"}\",\"text\":\"Mobile Telephone Number\",\"type\":\"control_phone\",\"answer\":{\"country\":\" 44\",\"area\":\"7499\",\"phone\":\"90377\"},\"prettyFormat\":\"( 44) (7499) 90377\"},\"15\":{\"name\":\"applicationSession\",\"order\":\"13\",\"text\":\"Application Session\",\"type\":\"control_textbox\",\"answer\":\"2020AC\"},\"16\":{\"name\":\"uniteAgent\",\"order\":\"14\",\"text\":\"UNIT-e Agent reference\",\"type\":\"control_textbox\",\"answer\":\"IFCELS\"},\"17\":{\"name\":\"uniteProgramme\",\"order\":\"15\",\"text\":\"UNIT-e Programme code\",\"type\":\"control_textbox\",\"answer\":\"Q3EP\"},\"18\":{\"name\":\"entryYear\",\"order\":\"16\",\"text\":\"Entry year\",\"type\":\"control_textbox\",\"answer\":\"2021\"},\"19\":{\"name\":\"entryMonth\",\"order\":\"17\",\"text\":\"Entry month\",\"type\":\"control_textbox\",\"answer\":\"04\"},\"20\":{\"name\":\"entryPoint\",\"order\":\"18\",\"text\":\"Entry point\",\"type\":\"control_textbox\",\"answer\":\"1\"},\"26\":{\"name\":\"postalzipCode\",\"order\":\"9\",\"text\":\"Postal\\/Zip Code\",\"type\":\"control_textbox\",\"answer\":\"KT1 3ER\"},\"27\":{\"name\":\"uniqueID\",\"order\":\"19\",\"selectedField\":\"528c8b464b1a424916000004\",\"text\":\"Unique ID\",\"type\":\"control_autoincrement\",\"answer\":\"8TYZ2Y\"}}}"

Many Thanks,

CodePudding user response：

In your case, the JSON that you get from the API contains \ characters in the JSON, so you need to deserialize as shown in the below attempt. Your Model structure will hold the dynamic nodes also.

For this, you need to use the Newtonsoft package which serializes and deserializes any .NET object. You can add this package through the Nuget manager in Visual Studio or add the reference manually in your project.

We also need a custom converter in your case to handle scenario when your Answer property can be a string or of Answer class type. It will set the value of Answer to null if it is a string and handle it accordingly.

A working example: https://dotnetfiddle.net/yB1fIf

using System;
using System.Collections.Generic;
using Newtonsoft.Json;
                    
public class Program
{
    public static void Main()
    {
        var myString=@" "; //omitted for breveity 
        var myDeserializedClass = JsonConvert.DeserializeObject<dynamic>(myString);
        Root myDeserializedClass1 = JsonConvert.DeserializeObject<Root>(myDeserializedClass);
    
        foreach(var items in myDeserializedClass1.content)
        {
            foreach(var items1 in items.answers.Values)
            {
                Console.WriteLine(items1.name);
                //Console.WriteLine(items1.text);
            }
        } 
        
    }
}

public class AnswersData
{
    public string name { get; set; }
    public string order { get; set; }
    public string sublabels { get; set; }
    public string text { get; set; }
    public string type { get; set; }
    [JsonConverter(typeof(AnswerConverter))]
    public Answer answer { get; set; }
    public string prettyFormat { get; set; }
    public string selectedField { get; set; }
    public string full { get; set; }
}

public class Answer
{
    public string full { get; set; }
}


public class Content
{
    public string id { get; set; }
    public string form_id { get; set; }
    public string ip { get; set; }
    public string created_at { get; set; }
    public string status { get; set; }
    public string @new { get; set; }
    public string flag { get; set; }
    public string notes { get; set; }
    public object updated_at { get; set; }
    public Dictionary<string,AnswersData> answers { get; set; }
}

public class ResultSet
{
    public int offset { get; set; }
    public int limit { get; set; }
    public int count { get; set; }
}

public class Root
{
    public int responseCode { get; set; }
    public string message { get; set; }
    public List<Content> content { get; set; }
    public string duration { get; set; }
    public ResultSet resultSet { get; set; }
}

public class AnswerConverter : JsonConverter<Answer>
{
    public override Answer ReadJson(JsonReader reader, Type objectType, Answer existingValue, bool hasExistingValue, JsonSerializer serializer)
    {
        switch (reader.MoveToContentAndAssert().TokenType)
        {
            case JsonToken.Null:
            case JsonToken.String:
                return null;

            default:
                var alarm = hasExistingValue ? existingValue : (Answer)serializer.ContractResolver.ResolveContract(objectType).DefaultCreator();
                serializer.Populate(reader, alarm);
                return alarm;
        }
    }

    public override bool CanWrite => false;

    public override void WriteJson(JsonWriter writer, Answer value, JsonSerializer serializer) => throw new NotImplementedException();
}

public static partial class JsonExtensions
{
    public static JsonReader MoveToContentAndAssert(this JsonReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException();
        if (reader.TokenType == JsonToken.None)       // Skip past beginning of stream.
            reader.ReadAndAssert();
        while (reader.TokenType == JsonToken.Comment) // Skip past comments.
            reader.ReadAndAssert();
        return reader;
    }

    public static JsonReader ReadAndAssert(this JsonReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException();
        if (!reader.Read())
            throw new JsonReaderException("Unexpected end of JSON stream.");
        return reader;
    }
}

Output:

standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID
standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID




standardForm
submit2
familyName
givenName
title
gender
dateOf
nationality
address
emailAddress
telephoneNumber
mobileTelephone
applicationSession
uniteAgent
uniteProgramme
entryYear
entryMonth
entryPoint
postalzipCode
uniqueID

CodePudding user response：

After much head-cracking, I finally managed to get Newtonsoft working. I used https://json2csharp.com/ to generator classes e.g;

    public class Root
    {
        [JsonProperty("content")]
        public List<Content> content { get; set; }
    }
    public class Content
    {
        [JsonProperty("answers")]
        public Answers answers { get; set; }
    }

    public class Answers
    {
        [JsonProperty("3")]
        public familyName familyName { get; set; }
    }
    public class familyName
    {
        [JsonProperty("answer")]
        public string answer { get; set; }
    }

I then deserialized like this;

dynamic jsonObject = JsonConvert.DeserializeObject<Root>(response);

This then allowed me to add each column to the Output0Buffer. Many thanks for your help and here's hoping this also assists someone.

Page link：https//www.codepudding.com/Mobile/311330.html

Prev:Flutter HTTP Post Request does not reach Node-RED on Raspberry Pi
Next:Linear layout in android Fragment
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding