locked
variables not updated RRS feed

  • Question

  • hi everyone. i have a problem. when i run my ssis, my variables are not set. please help.

    this is my code:

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

        Public Sub Main()
            SetReportName()
            Dts.TaskResult = Dts.Results.Success
        End Sub

        Private Function WriteVariable(ByVal varName As String, ByVal varValue As Object) As Boolean
            Dim vars As Variables

            Try
                If (Dts.VariableDispenser.Contains(varName)) Then
                    Dts.VariableDispenser.LockForWrite(varName)
                    Dts.VariableDispenser.GetVariables(vars)

                    vars(varName).Value = varValue
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                Throw ex
            Finally
                If Not (vars Is Nothing) Then
                    vars.Unlock()
                End If
            End Try
        End Function


        Private Sub SetReportName()

            Dim ReportId As String = Trim(RdVarStr("Report_Id"))
            Dim ReportFreq As String = Trim(RdVarStr("Report_Freq"))
            Dim Report_Name As String = ReportId & "_" & ReportFreq
            Dts.Log("Report_Name: " & Report_Name, 0, Nothing)
            WriteVariable("Report_Name", Report_Name)


        End Sub

        Private Function RdVarStr(ByVal varName As String) As String
            Return CType(ReadVariable(varName), String)
        End Function

        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
                    Throw ex
                Finally
                    vars.Unlock()
                End Try
            Catch ex As Exception
                Throw ex
            End Try
            Return result
        End Function


    End Class

     

    when i view my variables when it is running, the report_name is blank.

    please help thanks

    Tuesday, November 8, 2011 2:02 AM

Answers

  • You can't see values update in variables pane. Variables store values until SSIS package executing.
    • Marked as answer by noobie1 Tuesday, November 8, 2011 5:41 AM
    Tuesday, November 8, 2011 4:14 AM

All replies

  • You can't see values update in variables pane. Variables store values until SSIS package executing.
    • Marked as answer by noobie1 Tuesday, November 8, 2011 5:41 AM
    Tuesday, November 8, 2011 4:14 AM
  • Put a msgbox(@Var1) in your script to see value of variable at run time.
    Tuesday, November 8, 2011 8:53 AM