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.Solution:
Step 1: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
Step 2:
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.
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
Dim rowNumber As Int32
Public Overrides Sub PreExecute()
rowNumber = Me.Variables.seqno
MyBase.PreExecute()
' Add your code here for preprocessing or remove if not needed
''
End Sub
Public Overrides Sub PostExecute()
MyBase.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
''
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
rowNumber += 1
Row.Seqno = rowNumber
End Sub
End Class
Final Output:
I have added dataviewer to show the data. Name is my input column and Seqno is created in Script Component.