SSIS Script Component, Allow Null values

Question

I have a SSIS package that I am programming and my script component won't allow null column inputs. I have checked the box to keep nulls in the flat file source component. My program is running well until my script component where I get the error "The column has a null value" (super vague, I know). The column currently throwing the error is an "int" valued column and is used for aggregations in my script.

I could make the null values 0s or to say "NULL" but I'd prefer to just leave them blank.

I am using SQL Server BIDS 2008.

Solution 1

So because SSIS deals with the databases so much and doesn't want to spend a lot of time differentiating between DB NULL and C# NULL, they create boolean properties for each input column in the Buffer with the naming convention (columnname)_IsNull. You can read more about that on MSDN.

So you have to use those buffer columns to determine whether the value is null and then doing whatever you're trying to do with that column in the component.

So something like

if (!Row.MyColumn_IsNull) { 
//do something }
else {
//do something else, or nothing, etc.
}

Solution 2

While Kyle's answer is very sufficient, I used a different method that is working just fine. I used the ternary for c#.

Value = Value_IsNull ? True Value : False Value;

Row.rowname = Row.rowname_IsNull ? 0 : Row.rowname;

This changed the value of my null integer columns to 0 if they were null coming into my script. Otherwise, it retained the value.

Comments

[user2471943]

Thank you, Kyle! That was what I tried to do originally and couldn't seem to catch the input before the error was thrown. I ended up using the ternary operation that I am going to put as another answer on here.

[Scotch]

I would advise against this unless you have a particular reason for it. 0 is usually a very different meaning than null.