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\":\"mmyers@gmail.com\"},\"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,
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.