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:

varvalue

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:

varvalue2

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!

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in Beginners, BI, dev, SSIS. Bookmark the permalink.

5 Responses to Checking the Value of a Variable in SSIS

  1. JamieT says:

    Why not just use a watch window?

  2. Use a what?;-)The reason that I sometimes like to use the FireInformation() method described here as opposed to setting breakpoints and using a Watch window or the Locals window is because I generally prefer to let the package keep running and just look at the output, as opposed to having to break execution to interrogate the variables.But you are correct – there are tools built into Visual Studio that often make it unnecessary to write any code. I should have added a disclaimer to that effect to the top of my post. 😉

  3. chenthorn says:

    Great post, its exactly what I want to do. in what kind of toolbox item would I enter this code into to get it to run? Script task?thansk!

  4. @chenthorn – Yes, this code would go into a Script Task.

  5. ibeckett says:

    This comment has been removed by the author.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s