Date: 07/24/07 (SQL Server)    Keywords: sql, web, microsoft

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:

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

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

Source: http://community.livejournal.com/sqlserver/61240.html