Jan 12, 2010

Assigning variables in Script Component

One of the sore points of using Script component is assigning value to variables.
Read Write variables can only be altered in Post Execute phase. Script Component runs the code for each record and this would affect performance adversely. For details check "Coding and Debugging the Script Component".
In case I need to assign the value of system variable "PackageName" to a readWrite user variable "PkgNm"

Here is a sample code to assign value to Read write variable on the post execute phase:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
ImportsMicrosoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()
       ' I have not added any code here
    End Sub
    Public Overrides Sub PostExecute()
        Me.Variables.PkgNm = Me.Variables.PackageName
        Msgbox(Me.Variables.PkgNm)
        MyBase.PostExecute()
    End Sub
End Class