SSIS API - handle Null in Script Component Source

Question

I am trying to write a script component to parse the list of JSON and put it in database with the help of Source Script Component.

Sample data looks something like this :

[{
    "name": "FP(ETU)-75P",
    "id": 6524,
    "categoryName": "Accelerator",
    "categoryId": 7,
    "specificGravity": "0.87"
},
{
    "name": "Mold Type",
    "id": 6238,
    "categoryName": null,
    "categoryId": null,
    "specificGravity": null
},
{
    "name": "Press Type",
    "id": 6239,
    "categoryName": null,
    "categoryId": null,
    "specificGravity": null
},
{
    "name": "Screw Temperature",
    "id": 6241,
    "categoryName": null,
    "categoryId": null,
    "specificGravity": null
}]

So far I have this error while executing,

Since there are nulls with every different type of outputs (String, Float and Int), what is the best way to handle this.

Also, this is what I have so far,

  #region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web.Script.Serialization;
#endregion

#region Class
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    /// <summary>Outputs records to the output buffer</summary>
    public override void CreateNewOutputRows()
    {
        
        //Set Webservice URL
        string wUrl = "https://app.uncountable.com/api/materials/6/3/0/customer/get_internal_data?apiId=2cde7aa2b98611acbb40fc5bc365b8be1cbca94a26ce6649301e8300d&apiSecretKey=b34f63919ff886d9c280ddab7fb7cef2f6d08634cad40122db9f05bcc333&data=%7b%22dataType%22:%20%22ingredients%22%7d";

        try
        {
            //Call getWebServiceResult to return our WorkGroupMetric array
            Ingredient[] outPutMetrics = GetWebServiceResult(wUrl);

            //For each group of metrics output records
            foreach (var metric in outPutMetrics)
            {
                Output0Buffer.AddRow();
                Output0Buffer.name = metric.name;
               // Output0Buffer.CategoryName = metric.CategoryName;
                Output0Buffer.id = metric.id;

                
               // TRIM(Output0Buffer.SpecificGravity) = metric.specificGravity;
            }

        }
        catch (Exception e)
        {
            FailComponent(e.ToString());
        }

    }

    /// <summary>
    /// Method to return our WorkGroupMetric array
    /// </summary>
    /// <param name="wUrl">The web service URL to call</param>
    /// <returns>An array of WorkGroupMetric composed of the de-serialized JSON</returns>
    private Ingredient[] GetWebServiceResult(string wUrl)
    {

        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
        Ingredient[] jsonResponse = null;

        try
        {
            //Test the connection
            if (httpWResp.StatusCode == HttpStatusCode.OK)
            {

                Stream responseStream = httpWResp.GetResponseStream();
                string jsonString = null;

                //Set jsonString using a stream reader
                using (StreamReader reader = new StreamReader(responseStream))
                {
                    jsonString = reader.ReadToEnd().Replace("\\", "");
                    reader.Close();
                }

                //Deserialize our JSON
                JavaScriptSerializer sr = new JavaScriptSerializer();
                //JSON string comes in with a leading and trailing " that need to be removed for parsing to work correctly
                //The JSON here is serialized weird, normally you would not need this trim
                jsonResponse = sr.Deserialize<Ingredient[]>(jsonString);

            }
            //Output connection error message
            else
            {
                FailComponent(httpWResp.StatusCode.ToString());

            }
        }
        //Output JSON parsing error
        catch (Exception e)
        {
            FailComponent(e.ToString());
        }
        return jsonResponse;

    }

    /// <summary>
    /// Outputs error message
    /// </summary>
    /// <param name="errorMsg">Full error text</param>
    private void FailComponent(string errorMsg)
    {
        bool fail = false;
        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
        compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);

    }

}
#endregion

#region JSON Class
//Class to hold our work group metrics
class Ingredient
{
    public string name { get; set; }

    public string CategoryName { get; set; }

    public int id { get; set; }

    public float specificGravity { get; set; }

    public int categoryId { get; set; }
}
#endregion

thanks in advance

Answer 1

Based on your supplied data, if you redefine your class to be

class Ingredient
{
    public string name { get; set; }
    public string CategoryName { get; set; }
    public int? id { get; set; }
    public float? specificGravity { get; set; }
    public int? categoryId { get; set; }
}

and then when you're adding a new row to the buffer, for the nullable fields you'd have logic like

if metric.CategoryName != null
{
    Output0Buffer.CategoryName = metric.CategoryName.Value;
}

No else is required as the IsNull flag will be set by virtue of not populating data. Otherwise, it's something like

else
{
    Output0Buffer.CategoryName_IsNull = True;
}

A fully working Script Source Component follows

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        Ingredient[] outPutMetrics = new Ingredient[]
        {
            new Ingredient() { name = "Zero", CategoryName = "myCat", id = 1, specificGravity = 1.3f, categoryId = 10 },
            new Ingredient() { name = "One", CategoryName = "myCat", id = 2, specificGravity = 1.3f, categoryId = 10 },
            new Ingredient() { name = "Two", CategoryName = "NoId", id = null, specificGravity = 1.3f, categoryId = 30 },
            new Ingredient() { name = "Three", CategoryName = "myCat", id = 4, specificGravity = null, categoryId = 400 },
            new Ingredient() { name = "Four", CategoryName = "None", id = null, specificGravity = null, categoryId = null }
        };
        foreach (var metric in outPutMetrics)
        {
            Output0Buffer.AddRow();
            Output0Buffer.name = metric.name;
            Output0Buffer.categoryName = metric.CategoryName;
            if (metric.id != null)
            {
                Output0Buffer.id = metric.id.Value;
            }

            if (metric.specificGravity != null)
            {
                Output0Buffer.specificGravity = metric.specificGravity.Value;
            }

            if (metric.categoryId != null)
            {
                Output0Buffer.categoryId = metric.categoryId.Value;
            }
        }
    }
}

public class Ingredient
{
    public string name { get; set; }
    public string CategoryName { get; set; }
    public int? id { get; set; }
    public float? specificGravity { get; set; }
    public int? categoryId { get; set; }
}

Results of running

The default project type lists a target Framework of 4.5 for SSIS 2017. SSIS is built against the .NET Framework which is C# 7.3. However, C# 7.3 could also refer to .NET Core 2.x. Core is a different and incompatible target framework to the .NET Framework. Reference: C# language versioning

I propose you comment out Ingredient[] outPutMetrics = GetWebServiceResult(wUrl); in favor of my static definition of a set of ingredients and confirm that works.

If it does, then you know any errors being raised are in the GetWebServiceResult and likely have anything to do with nullable primitive types.