C# SSIS How to iterate through columns and set to null

0
votes

I need to set any columns that are empty strings to an actual null value in SSIS

Normally I can just use a Derived Column and set it to NULL(DSTR, 18, 1252)

Since I have loads of fields and want to do it in one go, I have decided to use a script component to do this. This is the code so far

foreach (PropertyInfo inputColumn in Row.GetType().GetProperties())
{
    if (!inputColumn.Name.EndsWith("IsNull")
    { 
        if (inputColumn.GetValue(Row, null).ToString().Equals(""))
        {                       
            inputColumn.SetValue(Row, null, null);
        }
    }
}

But it throws this error:

[Script Component [11692]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at
Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at
Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

How can I set it to the equivalent of NULL(type) in a derived column?

1
c#ssisscript-component
I found somewhere that by setting the X_IsNull to true, that also works, but how do I incorporate that? – Skids

1 Answers

0
votes

Further to my comment, just have to set the equivalent _IsNull property to true.

Row.GetType().GetProperty(inputColumn.Name + "_IsNull").SetValue(Row, true, null);