SSIS: Object reference not set to an instance of an object Error

[dmbuso]

I'm trying to use a script component to manipulate data in an Access database
to do some custom processing and output to an output row so that I can direct
the output rows to say another Access database.

To accomplish this, I created a script componet in the Data Flow section of
SSIS, checked the Available Input columns that I wanted, created Output
columns of the appropriate data types, and created a script to do the custom
processing.

When I run the package, I receive the error "Object reference not set to an
instance of an object".

Below is the contents of ScriptMain.

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'

Try
'Created by DMB (2/28/08)
Static poeID As Integer
Static poeName As String

'Is input row all NULL or all NULL except for field F1?
If (Row.F3_IsNull And Row.F4_IsNull And Row.F5_IsNull And
Row.F6_IsNull And Row.F7_IsNull And Row.F8_IsNull) Then
'Skip current row
Exit Sub
End If

'Create new output record
Me.Output0Buffer.AddRow()

'Set output record fields
If (InStr(1, Row.F1.ToString, "-", vbTextCompare) > 0) Then
'You're on the first row (or a row with only F1 filled in)
Me.Output0Buffer.POEID = CInt(Mid(Row.F1.ToString, 1,
InStr(1, Row.F1.ToString, "-") - 2))
Me.Output0Buffer.POENAME = Mid(Row.F1.ToString, InStr(1,
Row.F1.ToString, "-") + 2)

'Save these in memvars for 2nd row
poeID = CInt(Mid(Row.F1.ToString, 1, InStr(1,
Row.F1.ToString, "-") - 2))
poeName = Mid(Row.F1.ToString, InStr(1, Row.F1.ToString,
"-") + 2)

'Me.Output0Buffer.SECTORCODE = ""
'Me.Output0Buffer.SECTORNAME = ""
Me.Output0Buffer.SEIZUREMONTH = "December"
Me.Output0Buffer.SEIZUREYEAR = "2007"

'The first line contans the # of lines which equal SEIZ
fields, so set these fields
Me.Output0Buffer.COCSEIZ = CDbl(IIf(Row.F3_IsNull, 0, Row.F3))
Me.Output0Buffer.HERSEIZ = CDbl(IIf(Row.F4_IsNull, 0, Row.F4))
Me.Output0Buffer.ECSSEIZ = CDbl(IIf(Row.F5_IsNull, 0, Row.F5))
Me.Output0Buffer.MARSEIZ = CDbl(IIf(Row.F6_IsNull, 0, Row.F6))
Me.Output0Buffer.ICESEIZ = CDbl(IIf(Row.F7_IsNull, 0, Row.F7))
Me.Output0Buffer.METSEIZ = CDbl(IIf(Row.F8_IsNull, 0, Row.F8))

'Don't set these fields, they're in the 2nd record
'Me.Output0Buffer.COCQTY = 0
'Me.Output0Buffer.HERQTY = 0
'Me.Output0Buffer.ECSQTY = 0
'Me.Output0Buffer.MARQTY = 0
'Me.Output0Buffer.ICEQTY = 0
'Me.Output0Buffer.METQTY = 0

Else
'You're on the second row
'Save these in memvars for 2nd row
Me.Output0Buffer.POEID = poeID
Me.Output0Buffer.POENAME = poeName

'Me.Output0Buffer.SECTORCODE = ""
'Me.Output0Buffer.SECTORNAME = ""
Me.Output0Buffer.SEIZUREMONTH = "December"
Me.Output0Buffer.SEIZUREYEAR = "2007"

'Don't set these fields, they're in the 1st record
'Me.Output0Buffer.COCSEIZ = CDbl(IIf(Row.F3_IsNull, 0,
Row.F3))
'Me.Output0Buffer.HERSEIZ = CDbl(IIf(Row.F4_IsNull, 0,
Row.F4))
'Me.Output0Buffer.ECSSEIZ = CDbl(IIf(Row.F5_IsNull, 0,
Row.F5))
'Me.Output0Buffer.MARSEIZ = CDbl(IIf(Row.F6_IsNull, 0,
Row.F6))
'Me.Output0Buffer.ICESEIZ = CDbl(IIf(Row.F7_IsNull, 0,
Row.F7))
'Me.Output0Buffer.METSEIZ = CDbl(IIf(Row.F8_IsNull, 0,
Row.F8))

'The second line contans the lbs. data which equal QTY, so
set these fields
Me.Output0Buffer.COCQTY = CDbl(IIf(Row.F3_IsNull, 0, Row.F3))
Me.Output0Buffer.HERQTY = CDbl(IIf(Row.F4_IsNull, 0, Row.F4))
Me.Output0Buffer.ECSQTY = CDbl(IIf(Row.F5_IsNull, 0, Row.F5))
Me.Output0Buffer.MARQTY = CDbl(IIf(Row.F6_IsNull, 0, Row.F6))
Me.Output0Buffer.ICEQTY = CDbl(IIf(Row.F7_IsNull, 0, Row.F7))
Me.Output0Buffer.METQTY = CDbl(IIf(Row.F8_IsNull, 0, Row.F8))

End If

Catch ex As Exception
MsgBox(ex.Message)
Exit Sub

End Try

End Sub

Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called
"<Output Name>Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'

End Sub

End Class

--
Dave B.

[Charles Wang[MSFT]]

Hi,
I understand that when you executed your script component, you encountered
the error of "object reference not set to an instance".
If I have misunderstood, please let me know.

This issue seems to be closely related to VB.NET programming. Anyway I
recommend you first debug your script to first identify which line threw
this exception and then let us know the place for further research. You can
refer to the following articles for debugging script:
Debugging Script
http://msdn2.microsoft.com/en-us/library/ms142157.aspx
How to: Set a Breakpoint in Script in a Script Task
http://msdn2.microsoft.com/en-us/library/ms140033.aspx

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[dmbuso]

As I stated in line 1 of my message, I'm using a script component (in the
Data Flow section) not a script task in the Work Flow section, therefore I'm
unable to set breakpoints and determine which line causes the exception.

However, I can try other methods talked about in MSDN article "Coding and
Debugging the Script Component"
(http://msdn2.microsoft.com/en-us/library/ms136033.aspx) to determine the
suspect code.

I guess I was hoping for some help from Microsoft, I'm kind of stuck here.

--
Dave B.

[Charles Wang[MSFT]]

Hi Dave,
I apologize that I did not pay attention to the fact that you were using a
script component for which debugging with breakpoints is not supported.

To trace the root cause of this issue, I recommend that you use
FireInformation (mentioned in the article "Coding and Debugging the Script
Component") before every place you reference an object. Regarding your code
snippet, I recommend that you use it at the following places:
1. Before your first If statement, check if your passed row 'Row' is null;
2. Check if the column F1 is null before you use it.

You can also have your exception message display more information by using:
MsgBox(ex.ToString())

Hope this helps.

[dmbuso]

Charles,

Thanks for the tips. I discovered that the F1 field was null. After I put in
checking for Nulls, the script worked great.

Thanks again for the help. Consider this closed.

--
Dave B.

[Charles Wang[MSFT]]

Hi Dave,
Thanks for your response.

I am very glad to hear that this issue has been resolved. If you have any
other questions or concerns, please feel free to let us know. Have a nice
day!