Here is the next in my series of posts explaining how common tasks in DTS can now be implemented in SSIS.
Capture a value from inside the data pump into a variable
SSIS enables us to write a value to a variable using the script component. Often when people do this they attempt it in the main data processing method of the component. Unfortunately that is not possible as it is not considered efficient to write to a variable when processing each incoming row due to the "last one wins" effect. Instead, writing to a variable is done in the PostExecute() method. Here is a simple example of using a synchronous script component to write the number of rows passing through the component to a variable. You will notice that the variable write is done in PostExecute():
|
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
'Declare a variable that is local to the script component code
Dim i As Integer
Public Overrides Sub PreExecute()
'Initialise the variable
i = 0
MyBase.PreExecute()
End Sub
'Input0_ProcessInputRow is called on each incoming row
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Increment the variable
i += 1
End Sub
Public Overrides Sub PostExecute()
Dim vars As IDTSVariables90
'Lock a variable so that we can write to it
Me.VariableDispenser.LockOneForWrite("RowCount", vars)
'Write the value to the variable
vars(0).Value = i
'Release the lock
vars.Unlock()
MyBase.PostExecute()
End Sub
End Class |
There are other ways of accomplishing this as you will see if you click here.