Allocating a 'Set ID' & Extracting data in groups

Question:
Allocate a Set ID for a Batch of data and make a group.
OR
Retain Values from Previous Rows.

Following is the input data and desired output for that

Answer:

Drag a DFT in the control flow and add the following components.

For achieving this you need to use Script Component in DFT. and the following below steps: Before that you need to select Header as input column.

Create an output column in script component

Add the following code in the Script component.
'*******************************************************************************
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Inherits UserComponent
Dim int_Sno as integer ' // this variable should be global for the script (class level)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If Row.RecType = "A" Then
int_Sno = int_Sno + 1
End If
Row.Sno = int_Sno

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
******************************************************************

Now execute your package and you will get the desired output (Given below)



Hope this will help you.