Finding the value of a variable in SSIS

SSIS has a number of debugging tools. The one I could not find myself was a tool to trace variable values at runtime. A quick search on the internet did wonders.

It is often useful to know which value a variable gets while the package runs. This simple little script, which you put in a script task, does the job. So you do not need to know anything about programming. First create a script task. Put it somewhere at the end of your Control Flow.

Then type in the script below:

{geshi xml:lang=“vbnet” lines=“false”}
Public Sub Main()

Dim variableName As String = “User::VandaagDatumID”

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 VandaagDatumID variable {0} is ‘{1}’”, variableName, variableValue), _
String.Empty, -1, False)

Dts.TaskResult = Dts.Results.Success

End Sub{/geshi}

{highslide type=“img” url=“artikelen/ssis/script_variable_find/script_createscript.jpg” width=600 captionText=‘Visual Basic Script’}{/highslide}

Do change the name of the variable (VandaagDatumID). If you then run the ETL package, you will see the value of the variable in the output window.

{highslide type=“img” url=“artikelen/ssis/script_variable_find/script_outputwindow.jpg” width=600 captionText=‘Output Windows SSIS’}{/highslide}