Assigning values to variables in SSIS
Two questions in one week. Sheesh. I apologize in advance.
As someone on the MS forums said, assigning values to variables in SSIS is way harder than it needs to be. I've read about fifty webpages on this, and they all boil down to the same two basic solutions, and both give me the same errors. I've tried this two different ways:
...and...
...and get the same results with each. Those results:
if the read-only and read-write sections of the script task are blank, I get a "the element cannot be found in a collection" exception. If I add the variables to the script task r-o and r-w fields, I get deadlock. Can someone tell me what I'm doing wrong? (I have found the Set Variable custom task, and I love it, but this code will be going on a production server owned by another company, so I can't install custom tasks to it.) Knowing me, it's something obvious.
Grazie...
As someone on the MS forums said, assigning values to variables in SSIS is way harder than it needs to be. I've read about fifty webpages on this, and they all boil down to the same two basic solutions, and both give me the same errors. I've tried this two different ways:
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
'
Dim vars As Variables
Dts.VariableDispenser.LockOneForWrite("intTYPeriodFrom", vars)
vars(0).Value = CInt(Dts.Variables("intThisWeekSysno").Value) - 51
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub...and...
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
WriteVariable("intTYPeriodFrom", CInt(Dts.Variables("intThisWeekSysno").Value) - 51)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Integer)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub...and get the same results with each. Those results:
if the read-only and read-write sections of the script task are blank, I get a "the element cannot be found in a collection" exception. If I add the variables to the script task r-o and r-w fields, I get deadlock. Can someone tell me what I'm doing wrong? (I have found the Set Variable custom task, and I love it, but this code will be going on a production server owned by another company, so I can't install custom tasks to it.) Knowing me, it's something obvious.
Grazie...
