' Listing 2: Custom script that manipulates string values

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)

' ******* BEGIN CALLOUT A *******
        ' Declare and set variable to hold prefix
        Dim ProdPrefix As String
        ProdPrefix = UCase(Row.ProductNumber.Substring(0, 2))
'  ******* END CALLOUT A *******

'  ******* BEGIN CALLOUT B *******
        ' If product length is greater than 7, determine product type,
        ' otherwise set product type to 'Misc'
        If Row.ProductNumber.Length > 7 Then
' ******* END CALLOUT B *******

' ******* BEGIN CALLOUT C *******
            ' If product length is 10, retrieve product size and determine if bike
            ' or frame, otherwise retrieve size and set product type to 'Clothes'
            If Row.ProductNumber.Length = 10 Then
                Row.ProdSize = UCase(Row.ProductNumber.Substring(8, 2))
                If ProdPrefix = "BK" Then
                    Row.ProdType = "Bike"
                ElseIf ProdPrefix = "FR" Then
                    Row.ProdType = "Frame"
                Else
                    Row.ProdType = "Misc"
                End If
            Else
                Row.ProdSize = UCase(Row.ProductNumber.Substring(8, 1))
                Row.ProdType = "Clothes"
            End If
        Else
            Row.ProdType = "Misc"
        End If
' ******* END CALLOUT C *******

' ******* BEGIN CALLOUT D *******
        ' Retrieve product category
        Row.ProdCategory = UCase(Row.ProductNumber.Substring(3, 4))
 ******* END CALLOUT D *******

    End Sub

End Class