I can't make this example work at all. I don't understand the statement "Row.CleanedZIP = zip" because there is no zip field that I can find. If you follow the example, as shown on page 232, there is indeed a CleanedZIP field but no Zip field.

I also suspect that the regular expressions shown on page 233 need some correction.

Any help appreciated,

barkingdog

I run into the same problem.



aspeng

I also ran into the same problem.

I created additional output fields CleanedZip and CleanedState.

I had to change the syntax of the regular expressions to get them to work(Detailed below).

The initial declaration of isGood needs to be set to "True" and not "False".

And finally, In the flat file source editor, on the default page(Connection Manager) the "Retain null values from the source as null values in the data flow" needs to be checked for the script to work correctly.

I hope this helps

Is is the full script below:

' 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 System.Text.RegularExpressions
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent
    Private zipRegex As Regex = New Regex("(^\d{5}$)|(^\d{5}-\d{4}$)", RegexOptions.None)
    Private stateRegex As Regex = New Regex("\b([A-z]{2})\b", RegexOptions.None)
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' All fields except zip must have a value
        Dim isGood As Boolean = True
        If Row.FirstName_IsNull Or Row.LastName_IsNull Or Row.City_IsNull Or Row.State_IsNull Then
            Row.GoodFlag = False
            Return
        End If

        If Not Row.Zip_IsNull Then
            Dim zip As String = Row.Zip.Trim()
            ' zip must match regex if present
            If zipRegex.IsMatch(zip) Then
                Row.CleanedZip = zip
                isGood = True
            Else
                ' try to clean up the zip

                If zip.Length > 5 Then
                    zip = zip.Substring(0, 5)
                    If zipRegex.IsMatch(zip) Then
                        Row.CleanedZip = zip
                        isGood = True
                    Else
                        isGood = False
                    End If
                End If
            End If
        End If
        If isGood Then
            Dim state As String
            state = Row.State.Trim().ToUpper()
            If stateRegex.IsMatch(state) Then
                Row.CleanedState = state
            Else
                isGood = False
            End If
        End If
        Row.GoodFlag = isGood
    End Sub

End Class

Thank you, your suggestions worked for me.