SSIS Convert Blank or other values to Zeros

Question

After applying the unpivot procedure, I have an Amount column that has blanks and other characters ( like "-"). I would like to convert those non-numberic values to zero. I use replace procedure but it only converts one at the time. Also, I tried to use the following script

/**

Public Overrides Sub Input()_ProcessInputRows(ByVal Row As Input()Buffer)
     If Row.ColumnName_IsNull = False Or Row.ColumnName = "" Then 
           Dim pattern As String = String.Empty
           Dim r As Regex = Nothing
           pattern = "[^0-9]"
           r = New Regex(pattern, RegexOptions.Compiled)
           Row.ColumnName = Regex.Replace(Row.ColumnName, pattern, "") 
      End If
End Sub

**/

but i'm getting error.I don't much about script so maybe I placed in the wrong place. The bottom line is that I need to convert those non-numberic values.

Thank you in advance for your help.

score 1 · Answer 1 · answered Sep 24 '13 at 21:24

I generally look at regular expressions as a great way to introduce another problem into an existing one.

What I did to simulate your problem was to write a select statement that added 5 rows. 2 with valid numbers, the rest were an empty string, string with spaces and one with a hyphen.

enter image description here

I then wired it up to a Script Component and set the column as read/write

enter image description here

The script I used is as follows. I verified there was a value there and if so, I attempted to convert the value to an integer. If that failed, then I assigned it zero. VB is not my strong suit so if this could have been done more elegantly, please edit my script.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    ' Ensure we have data to work with
    If Not Row.ColumnName_IsNull Then
        ' Test whether it's a number or not
        ' TryCast doesn't work with value types so I'm going the lazy route
        Try
            ' Cast to an integer and then back to string because 
            ' my vb is weak
            Row.ColumnName = CStr(CType(Row.ColumnName, Integer))
        Catch ex As Exception
            Row.ColumnName = 0
        End Try

    End If

End Sub

SSIS Convert Blank or other values to Zeros

1 Answers1