SSIS- Read Variable In Script Component and Generate Sequence Number

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.


1 comment: