September 16, 2008 at 4:55 am
I'm having a problem writing to variables using the script task in SSIS. I've tried every example I can find and even code my boss says works for him (which is simpler and just uses dts.variables(varName).value = value) and even this doesn't work. It just hangs on the task highlighted in yellow.
I've added the readonly variables to the ReadOnlyVariables (e.g. var1,var2,var3) and the variables i want to write to to the ReadWriteVariables and the code in the script is below
Public Sub Main()
Dim varName As String
Try
Dim intRows As Integer = CInt(ReadVariable("FileRows"))
MsgBox("intRows: " & intRows)
Dim intSplit As Integer = CInt(ReadVariable("intSplit"))
MsgBox("intSplit: " & intSplit)
Dim intLoops As Integer = 2
MsgBox("intLoops: " & intLoops)
Dim intFrom As Integer = 0
Dim variables As Variables
MsgBox("Begin write to variables")
varName = "FromPoint"
Dts.Variables("FromPoint").Value = intFrom
MsgBox("Write 1 complete")
varName = "ToPoint"
Dts.Variables(varName).Value = intSplit
MsgBox("Write 2 complete")
varName = "intLoops"
Dts.Variables(varName).Value = intLoops
MsgBox("Write 3 complete")
MsgBox("intFromId: " & intFrom)
MsgBox("intToId: " & intSplit)
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, "SetSiteVariables", ex.Message & " : " & varName, "", 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
As you can see i've put message boxes in for debugging purposes and it still doesn't run.
I've also tried a slightly more complicated script, but this hangs as well:
Public Sub Main()
Dim varName As String
Try
Dim intRows As Integer = CInt(ReadVariable("FileRows"))
MsgBox("intRows: " & intRows)
Dim intSplit As Integer = CInt(ReadVariable("intSplit"))
MsgBox("intSplit: " & intSplit)
Dim intLoops As Integer = 2
MsgBox("intLoops: " & intLoops)
Dim intFrom As Integer = 0
Dim variables As Variables
MsgBox("Begin write to variables")
WriteVariable("FromPoint", CStr(intFrom))
WriteVariable("ToPoint", CStr(intSplit))
WriteVariable("intLoops", CStr(intLoops))
MsgBox("intLoops: " & intLoops)
MsgBox("intFromId: " & intFrom)
MsgBox("intToId: " & intSplit)
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(0, "SetSiteVariables", ex.Message & " : " & varName, "", 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As String)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Dts.Events.FireError(0, "SetSiteVariables", ex.Message & " : " & varName, "", 0)
Dts.TaskResult = Dts.Results.Failure
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Dts.Events.FireError(0, "SetSiteVariables", ex.Message & " : " & varName, "", 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Dts.Events.FireError(0, "SetSiteVariables", ex.Message & " : " & varName, "", 0)
Dts.TaskResult = Dts.Results.Failure
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Dts.Events.FireError(0, "SetSiteVariables", ex.Message & " : " & varName, "", 0)
Dts.TaskResult = Dts.Results.Failure
End Try
Return result
End Function
End Class
PLEASE HELP!!!!
September 16, 2008 at 5:44 pm
You should only write to the variables in the post execute method.
Overwrite it, and you don't need to use variabledispenser in the postexecute method, only in the main and preexecute methods. The preexecute is a better place for reading the variables as well since it's executed once, first.
September 16, 2008 at 5:45 pm
Public Class ScriptMain
Inherits UserComponent
Private localLineNbr As Integer
Private localSeqNbr As Integer
Public Overrides Sub PreExecute()
'Read in Global Variables
Try
Dim preVars As IDTSVariables90 ' DTS Source Compenent uses Variables90 (plural)
VariableDispenser.LockForRead("LineNbr") 'Gotta lock those variables in parallel tasks even if it's read-only here
VariableDispenser.LockForRead("SeqNbr")
VariableDispenser.GetVariables(preVars) ' It grabs the one you locked in the variables object
Try
localLineNbr = CInt(preVars("LineNbr").Value) 'Save it locally
localSeqNbr = CInt(preVars("SeqNbr").Value)
Catch ex As Exception
Throw ex
Finally 'Let it go on it's merry way. No matter what!
If preVars.Locked Then
preVars.Unlock() 'Let it go on it's merry way. No matter what!
Else
VariableDispenser.Reset()
End If
End Try
Catch ex As Exception
Throw ex
End Try
MyBase.PreExecute()
End Sub
Public Overrides Sub PostExecute()
Try
Variables.LineNbr = localLineNbr 'Save it globally
Variables.SeqNbr = localSeqNbr
Catch ex As Exception
Throw ex
Finally
VariableDispenser.Reset()
MyBase.PostExecute()
End Try
End Sub
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply