IF OBJECT_ID('dbo.SSISTest') IS NOT NULL
	DROP TABLE dbo.SSISTest
GO
CREATE TABLE dbo.SSISTest(
	Name varchar(10) NULL,
	Value int NULL
) 
GO
INSERT INTO dbo.SSISTest
SELECT 'Ab1',23 UNION ALL
SELECT 'Ab1',25 UNION ALL
SELECT 'Ab1',26 UNION ALL
SELECT 'Ab2',23 UNION ALL
SELECT 'Ab2',25 UNION ALL
SELECT 'Ab3',26 

' 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
Option Strict Off
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)
        '
        Dim Counter As Integer
        Dim Name As String
        Dim vars As IDTSVariables90

        Me.VariableDispenser.LockOneForRead("User::Name", vars)
        Name = vars(0).Value
        vars.Unlock()

        Me.VariableDispenser.LockOneForRead("User::Counter", vars)
        Counter = vars(0).Value
        vars.Unlock()

        If Name = Row.Name Then
            Counter = Counter + 1
        Else
            Counter = 1
        End If

        ' Write values back to variables
        Me.VariableDispenser.LockOneForWrite("User::Name", vars)
        vars(0).Value = Row.Name
        vars.Unlock()

        Me.VariableDispenser.LockOneForWrite("User::Counter", vars)
        vars(0).Value = Counter
        vars.Unlock()

        ' Update row in pipeline
        Row.Name = Row.Name + "^" + Counter.ToString
    End Sub
End Class

IF OBJECT_ID('dbo.SSIS_Dest') IS NOT NULL
	DROP TABLE dbo.SSIS_Dest
GO
CREATE TABLE dbo.SSIS_Dest(
	Name varchar(10) NULL,
	Value int NULL
)
GO