Scenario :We got this requirement that we need to read the max Sequence Number value from Table and then generate sequence by adding 1 in Data Flow for our SequenceNumber column.
First we have to read the max(sequencenumber) from our table and store in some variable that we can use in script component later.
Bring Execute SQL Task on Control Flow pane and configure as shown below
Set the values to Variable seqno
Now bring data flow task to Control Flow pane. Double Click on Data Flow task and then bring the Source from which you need to read the data.
Bring Script Component to Data Flow Pane and then connect with your Source. We will be using Script task as transformation.
Configure the Script Component as shown below
Select the Scripting Language and ReadOnlyVariable
Choose the columns those you want to pass through
Add a new output Column Seqno and set the data type as shown
Go to General and Click on Edit Script and then make changes as shown in RED
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.
Public Class ScriptMain
Dim rowNumber As Int32
Public Overrides Sub PreExecute()
rowNumber = Me.Variables.seqno
' Add your code here for preprocessing or remove if not needed
Public Overrides Sub PostExecute()
' Add your code here for postprocessing or remove if not needed
' You can set read/write variables here, for example:
' Me.Variables.MyIntVar = 100
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
rowNumber += 1
Row.Seqno = rowNumber