SSIS read/write to variable in script task
I have a variable called Valint that i need to read/write to in a script task, but it doesn't seem to work:
public class scriptmain
inherits usercomponent
dim counter as integer
dim Valint as integer
.....
Public sub new()
counter = 0
end sub
public overrides sub input0_processintputrow(byval row as input0buffer)
Dim vars as IDTSvariables100 = nothing
Me.variableDispenser.lockforread("User::Valint")
Me.variableDispenser.GetVariables(vars)
counter = CType (vars("User:: Valint").Value, integer)
vars开发者_StackOverflow.Unlock()
counter +=1
Me.VariableDispenser.LockoneForWrite("User::Valint", vars)
vars("User::Valint").Value = counter
vars.Unlock()
End Sub
For some reason my output is always 0
This is in a data flow task, yes? You can't modify a variable in the script transformation, at least during the processinputrow subroutine.
Fortunately, it seems like the type of modification you are doing can be done-just in different subroutines.
My SSIS Scripting Beta book by Donald Farmer is a thousand miles from me so please forgive the imprecision of this code. The important thing is that you can only write to the user variable from the PostExecute event.
public class scriptmain
inherits usercomponent
dim counter as integer
dim Valint as integer
.....
Public sub new()
counter = 0
end sub
public overrides sub input0_processintputrow(byval row as input0buffer)
counter +=1
End Sub
Public overrides sub PostExecute()
Dim vars as IDTSvariables100 = nothing
Me.variableDispenser.lockforread("User::Valint")
Me.variableDispenser.GetVariables(vars)
counter = CType (vars("User:: Valint").Value, integer)
vars.Unlock()
Me.VariableDispenser.LockoneForWrite("User::Valint", vars)
vars("User::Valint").Value = counter
vars.Unlock()
End Sub
精彩评论