Checking the Value of a Variable in SSIS

It's a pretty common debugging and diagnostic task to need to check the value of a variable. The SSIS designers in Visual Studio do provide a set of debugging tools, but sometimes it's quicker and easier to get "low tech" and get the variable value yourself.

Back in the bad old days of VB 6 we would do something like this:

MsgBox g_VariableName

Back in the worse old days of ASP we would do something like this:

Response.Write(strVariableName)

And today in SSIS we can do something like this:

    Public Sub Main()

        Dim variableName As String = "User::VarTest"

        Dim vars As Variables = Nothing

        Dts.VariableDispenser.LockForRead(variableName)

        Dts.VariableDispenser.GetVariables(vars)

        Dim variableValue As String = CType(vars(variableName).Value, String)

        vars.Unlock()

        System.Windows.Forms.MessageBox.Show( _

            String.Format("Variable Value is '{0}'", variableValue), _

            String.Format("Value for Variable '{0}'", variableName))

        Dts.TaskResult = Dts.Results.Success

    End Sub

And (assuming we have a variable named VarTest in our package) when we run the package we'll get something like this:

Pretty simple, and it gets the job done. But there has to be a better way, right? No one wants an annoying modal dialog box popping up during package execution, even when they're developing and debugging. So you may want to do something like this instead:

    Public Sub Main()

        Dim variableName As String = "User::VarTest"

        Dim vars As Variables = Nothing

        Dts.VariableDispenser.LockForRead(variableName)
        Dts.VariableDispenser.GetVariables(vars)

        Dim variableValue As String = CType(vars(variableName).Value, String)

        vars.Unlock()

        Dts.Events.FireInformation(-1, "Test Script", String.Format( _
            "The value for variable {0} is '{1}'", variableName, variableValue), _
            String.Empty, -1, False)

        Dts.TaskResult = Dts.Results.Success

    End Sub

Now, when you run the package you will get something like this:

The output will be written to the Output window in Visual Studio and to any other log providers that you have configured to listen to information messages from the package.

Enjoy!