SSIS Script Component - Error:

Question

Post reply

SSIS Script Component - Error:

Ramdas Narayanan

Old Hand

Points: 340
More actions
October 4, 2011 at 1:24 pm

#146357

Hi folks,
In the script Component in the data flow task, i am getting a System.NullReferenceException Error: Object Reference not set. Any thoughts suggestions?
Here is the script:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlParam As SqlParameter
Public Overrides Sub PreExecute()
connMgr = Me.Connections.LookupConnection
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
sqlCmd = New SqlCommand("SELECT ContactID,FirstName,LastName FROM Contact WHERE(ContactID = @ContactID)", sqlConn)
sqlParam = New SqlParameter("@ContactID", SqlDbType.Int)
sqlCmd.Parameters.Add(sqlParam)
End Sub
Public Overrides Sub ContactInput_ProcessInputRow(ByVal Row As ContactInputBuffer)
Dim reader As SqlDataReader
sqlCmd.Parameters("@ContactID").Value = Row.ContactID
reader = sqlCmd.ExecuteReader()
If reader.Read() Then
'do all field comparisons here to determine if
' the record changed since the last ETL.
If (reader("FirstName").ToString() <> Row.FirstName) Then
Row.DirectRowToUpdateRecords()
End If
End If
reader.Close()
End Sub
Public Overrides Sub PostExecute()
connMgr.ReleaseConnection(sqlConn)
End Sub
End Class

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

What line is producing the error?

I'm guessing here:

sqlCmd.Parameters("@ContactID").Value = Row.ContactID

OR

If (reader("FirstName").ToString() <> Row.FirstName) Then

You might need to test for DBNull, using a command like:

If dataRow.Item("BlockSize") Is DBNull.Value Then

I chopped that out of a script I had..

CEWII

Ramdas Narayanan Old Hand Points: 340 More actions · Answer 2

Hi Elliot,

I did not the line number in the error output.

So in this line i need to check for a ISNULL Value?

sqlCmd.Parameters("@ContactID").Value = Row.ContactID

Thank you

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I'm guessing that the field value is null or nothing, are you sure there is a value in the field at that point?

CEWII

Ramdas Narayanan Old Hand Points: 340 More actions · Answer 4

The field values are coming from a text file, there are 3 rows and the first column is ContactID, there are no null values for this column.

I have added a attachment...