SSIS 101: Viewing Variable Values at Runtime

One of the common problems that beginners have with SSIS is debugging errors involving variables. One example of this occurs when a package uses a Foreach Loop container. These are often used to set a variable value differently for each iteration of a loop. If something fails during the loop, you might want to check the value of the variable in order to determine what went wrong.

Fortunately, this is pretty easy to accomplish in SSIS. You can see the value of any package variable in BIDS when you debug the package by following the steps below:

  1. First, set a breakpoint on a task where you’d like to check the current variable values. You can set a breakpoint by right-clicking on the task and choosing Edit Breakpoints.image
  2. Choose OnPreExecute to see values before the task executes and OnPostExecute to see them after execution. Click OK after enabling the breakpoint.
    image 
  3. Run the package in debug mode (press F5) in Visual Studio. The package will run until the breakpoint is hit.
    image
  4. Once execution stops at the breakpoint, open the Locals window (Ctrl+Alt+V, L or Debug..Windows..Locals)
    image
  5. Expand the Variable node in the Locals window. You can see the current values for all your variables, including system variables, in this window. You may have to scroll down to see your variables in the list
    image

This is a useful technique for troubleshooting packages that use variables, particularly if the variable values are changed during package execution.

12 Comments

  1. http:// says:

    Moreover, this technique allows you to see the value of variables that are evaluated as expressions and hence I always advocate putting expressions into variables rather than applying them to Container/ConnMan properties directly.

    -Jamie

  2. Aiden Drummond says:

    Major thankies for the blog article.Much thanks again.

  3. Jeff says:

    Hugely helpful…Thanks!!

  4. Jeff says:

    I was trying to find this everywhere. I have never had an issue with adding a date to a file with a .txt document so I tried the same process with excel and it was failing. I didn’t realize you need to create the worksheet first.

  5. Omakin says:

    Helpful and straight to the point! Good stuff….Thanks!!

  6. Ssozi says:

    Screenshots were very helpful!! along with the brief and to the point descriptions.

  7. Vincent says:

    Thanks, this was very helpful

  8. Thirmal says:

    Thanks, It’s Very useful…

  9. Matt says:

    Thanks , Very helpfull , it is concise and to the point (but stil show all the detail needed)

  10. Very good explantions with snapshots…. Helped me a lot..

  11. Deependra Singh says:

    Very nice solution.

Leave a Reply