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

Probably with a nullable version of your primitives ? but without seeing code, it's difficult to say. Alternatively, set the is null property on the Data Flow columns themselves and don't store to an intermediate value from the json — billinkc
– billinkc, Commented Dec 21, 2021 at 14:31
I tried with nullable but its giving me a version error of c# at 7.3. Can't get to update that to higher version. — Kaushik Shah
– Kaushik Shah, Commented Dec 21, 2021 at 18:41

billinkc · Accepted Answer · 2021-12-29 21:41:33Z

2

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.

edited Dec 29, 2021 at 21:41

answered Dec 21, 2021 at 20:36

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

KeithL Over a year ago

i can't believe i read his question entirely only to come to the same conclusion. Good Job

KeithL Over a year ago

one exception. move the ? in 3rd line to public int? id

billinkc Over a year ago

Thanks for keeping me honest @KeithL ;)

Kaushik Shah Over a year ago

When I try to create nullable variable as mentioned in the class, I am getting C# 7.3 not compatible error. I have spent hours trying to figure that out but no luck

billinkc Over a year ago

@KaushikShah Your provided problem statement indicates you have errors with NULL values. Where does C# 7.3 come into play?

|

Collectives™ on Stack Overflow

SSIS API - handle Null in Script Component Source

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related