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.