November 23, 2009 at 2:07 pm
I have a script component which does some string processing to data-fill derived fields, and I've wasted hours tracking down errors which result in '[font="Courier New"]Object reference not set to an instance of an object[/font]' without saying WHERE in the component the error is. By process of elimination, I've found that a number of string methods fail UNLESS you do some other operation first. Very odd - looks like a bug to me, but maybe I am doing something wrong; I'm new to VB.Net (after using VBA for years).
I've reduced the code to a simple example, which just trims the tailing blanks off POSTCODE before assigning the value to another field:
'' Usual preamble, then
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim Postcode As String
Postcode = Row.Postcode
Postcode = Postcode & " " ' Fails if this missing!
Postcode = Postcode.TrimEnd
Row.ID = Postcode
End Sub
This works - UNLESS I take out the redundant line, whereupon it fails with the above error message!
Postcode = Postcode & " "
It ought to be possible to do this in 1 line, but no; this fails too:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.ID = Row.Postcode.TrimEnd
End Sub
I don't think it's specific to my system, as I tried my code on a Windows Server 2005 system too, (running the same version of SSIS), and that had the same error.
So what am I doing wrong - or is it really a bug in VB?
Setup: SQL Server 2008 with SSIS / MS Visual Studio 9.0.30729.1 SP running on Windows XP
P.S. Vikas Bindra, if you're reading this, thanks for your previous advice; I followed your suggestion and it did the job perfectly.
November 24, 2009 at 1:28 am
Thinking about this overnight, I wonder if the problems is caused by NULLs in the data? (If that IS the reason, why does SSIS produce such a vague error message, when it could TELL you that you'd done something illegal with a NULL value, and name the variable in question; that would have saved me many hours!).
I'd expect that assigning a field value that was NULL to a string data type would set it to "" (empty string), OR maybe raise an error message at that point. But can VB.Net string hold a NULL value? If so, what happens later when you perform a string function on it? Could someone who knows say how VB.Net in SSIS behaves with NULL values; thanks.
November 26, 2009 at 5:11 pm
Just to close this, in case anyone else has a similar problem and wants a solution - yes it is because of NULLs in the data. I now know that the string data type can contain NULL, but that most string functions fail when fed a NULL. On solution to this is to check for NULL as you read in the data, and ensure that your strings do not contain any - e.g. don't write
Postcode = Row.Postcode ' This will fail when you apply string methods later if Postcode was NULL
Instead write e.g.
Postcode = If(Row.Postcode_IsNull, "", Row.Postcode)
or the multiline equivalent if using an older version, (that form of inline IF is new in SS 2008). Then if Row.Postcode was NULL, your local string variable will contain the empty string "", which unlike NULL does not upset the string functions like .Trim and .Replace
My unsafe one-line Sub example above could safely be replaced by
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Postcode = If(Row.Postcode_IsNull, "", Row.Postcode).TrimEnd
End Sub
December 1, 2009 at 9:31 am
I would think that the following will work as well
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Postcode = (Row.Postcode & "").TrimEnd
End Sub
Appending an empty string to the end of database fields that can be null is commonly used in VB to avoid errors caused by the null.
Steve
December 3, 2009 at 4:30 pm
Thanks, Steve; yes I'm sure that would work. And now that I know VB 2008 a little better, I know this form of IF would work too:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Postcode = If(Row.Postcode, "").TrimEnd
End Sub
That basically says 'If the field is NULL, return "", else return the field'.
This form of IF is new to SS 2008, so don't try it in SS 2005 and earlier.
January 26, 2010 at 1:48 am
how about
Postcode = Row.Postcode.ToStr
?
January 26, 2010 at 10:49 am
No. I agree it would be nice if that DID work, but it does not.
Firstly, it's [font="Courier New"]Postcode = Row.Postcode.ToString[/font], and secondly, you cannot apply the ToString method to an object which happens to be NULL. If you do, you'll get this error message:
"Object reference not set to an instance of an object."
Not a very helpful message, as it gives no indication of which object it means, or where it was not set. Best avoided!
Which you can do by ALWAYS testing Row values for NULL before doing anything else with them.
Hence using [font="Courier New"]If(Row.Postcode, "")[/font]. Or use [font="Courier New"]If(Postcode_IsNull ...)[/font] if you prefer. Or use the [font="Courier New"]Row.Postcode & ""[/font] trick as suggested above.
January 27, 2010 at 7:26 am
What really burns me up about the "error messages" is that the people who write the compilers know exactly what the "error" is so why can't they just say so instead of putting out something that is basically useless!:w00t:
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
January 28, 2010 at 2:03 pm
Indeed. Brilliant as BIDS is in some ways, in others it takes me back to the crude tools we had 40 years ago. "Unable to compile. Error in source code". 😉
Then there's the lack of support for globals - why can't we use shared functions and subroutines?
This discussion is getting rather off-topic though!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply