0

I have an ssis package that takes a flat file and dumps it to SQL. During that process, 150 columns need to have '-' and '.' removed from them. I have done this using a Script Transformation.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Dim column As IDTSInputColumn90
    Dim rowType As Type = Row.GetType()
    Dim columnValue As PropertyInfo

    For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection

        columnValue = rowType.GetProperty(column.Name)

        Dim strCol As String = columnValue.GetValue(Row, Nothing).ToString()
        If Not String.IsNullOrEmpty(strCol) Then
            strCol = strCol.Replace("-", "").Replace(".", "")
            columnValue.SetValue(Row, strCol, Nothing)
        End If
    Next
End Sub

I however get the following error when running it

Object reference not set to an instance of an object.

at ScriptComponent_f20c21e11e3348378ef0bbe6b65e9c05.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at ScriptComponent_f20c21e11e3348378ef0bbe6b65e9c05.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at ScriptComponent_f20c21e11e3348378ef0bbe6b65e9c05.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

I can't figure out why this is happening.. Any thoughts?

Edit I've discovered that this line is the problem

Dim strCol As String = columnValue.GetValue(Row, Nothing).ToString()
Joel Coehoorn
  • 362,140
  • 107
  • 528
  • 764
Matt
  • 3,838
  • 9
  • 36
  • 61
  • possible duplicate of [What is a NullReferenceException in .NET?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-in-net) – John Saunders Mar 01 '12 at 02:09
  • Not really. I understand NullRefExceptions.. but in this case I can't see it or figure out how to avoid it.. I've tried pre defining strCol as "bob", but I believe setting it to the GetValue of the column is causing the grief.. I can't figure out how to get around it – Matt Mar 01 '12 at 02:20
  • How to get around it? Something is set to `Nothing`. Find out what it is, then fix that. – John Saunders Mar 01 '12 at 02:21
  • I'm reading from a flat file.. some of the fields are blank.. I know this, but I don't know how to precheck if the field is blank before I try to assign it to my string – Matt Mar 01 '12 at 02:25
  • SSIS 2008 or 2005? I assume 2005 based on the reference to `IDTSInputColumn90` but just like to cover the bases. – billinkc Mar 01 '12 at 03:09
  • Not blank. `Nothing` is a totally different concept. – John Saunders Mar 01 '12 at 04:32
  • Well in my flat file which is a csv, it goes ,,,,,,,,, for a section. I assume that vb is reading that as Nothing. – Matt Mar 01 '12 at 04:57

1 Answers1

1

I was able to get it to work for my dataset. I skipped the cast to string type and just left it as object type based on what I assume to be the source of your script. It's not pretty but it seems to get the job done. 

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Dim column As IDTSInputColumn90
    Dim rowType As Type = Row.GetType()
    Dim columnValue As PropertyInfo

    For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection

        columnValue = rowType.GetProperty(column.Name)
        Dim meta As Integer = column.ExternalMetadataColumnID
        Try
            Dim Obj As Object = columnValue.GetValue(Row, Nothing)
            If Not String.IsNullOrEmpty(Obj) Then
                Obj = Obj.Replace("-", "").Replace(".", "")
                columnValue.SetValue(Row, Obj.ToString(), Nothing)
            End If
        Finally
            ' Rejoice in our null-ness
        End Try
    Next
End Sub

Source data

Starting with this data

SourceSSN,DestinationSSN,Amount
,111-11-0000,5000.00
111-22-3333,111-22-3334,5000.00
121-22-3333,121-22-3334,5000.00
123-22-3333,123-22-3334,5000.00
124-22-3333,124-22-3334,5000.00

The above script makes this

enter image description here

Community
  • 1
  • 1
billinkc
  • 54,852
  • 9
  • 98
  • 150