OK, I mocked up an example based off of this table:
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
Create 2 variables:
--USER::Name as string
--USER::Counter as int32
Then, I created a data flow. The first task inside the data flow is an OLE DB Source pointed to the dbo.SSISTest table. The next task is a Script Transformation with the following code:
' 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
I routed the script component to an OLE DB Destination linked to this table:
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
This is the results it produced for me:
Name Value
Ab1^123
Ab1^225
Ab1^326
Ab2^123
Ab2^225
Ab3^126