5

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
user2471943
  • 81
  • 1
  • 2
  • 5
  • Please post enough of the code of your component so that we can make a guess. – John Saunders Jun 27 '13 at 01:21
  • Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). – John Saunders Jun 27 '13 at 01:22

3 Answers3

11

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.
}
Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • 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. – user2471943 Jun 25 '13 at 23:34
3

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.

user2471943
  • 81
  • 1
  • 2
  • 5
  • I would advise against this unless you have a particular reason for it. 0 is usually a very different meaning than null. – Scotch Mar 09 '17 at 19:31
0

Is your error occurring in the C# code somewhere? It gets a little maddening at times when you have a database column with the datatype int, and it allows nulls. In C# land you have to use the int? as apposed to int when you need to accept nulls. Otherwise the approaches mentioned already are a good way to go.

Mike Henderson
  • 1,305
  • 15
  • 27