Checking SSIS Variable Values During Runtime

As anyone who reads my blog on a regular basis knows, I’m a big fan of using dynamic configurations, including SSIS parameters variables, to make my ETL architectures as flexible as possible. However, along with those dynamic behaviors comes the challenge of troubleshooting and debugging. Because variables and parameters do not have static values (by design), working your way through a failing or otherwise errant process becomes more complicated.

Fortunately, there is a way to peek in on parameter and variable values when debugging packages in SQL Server Data Tools. Using breakpoints and the Locals window, one can monitor these variables during execution to help identify potential issues in the package design.

Checking SSIS Variable and Parameter Values

There are two thing that must be done to monitor variable and parameter values for SSIS packages in SSDT:

• Set a breakpoint on the package to temporarily pause execution
• Show the Locals window during execution

To set up a breakpoint for any of the tasks or containers in a package, simply click that executable and press F9. When the breakpoint is enabled, the execution will pause until manually prompted to continue. As shown below, I have set up a breakpoint on the data flow inside the for each loop, indicated by the red circle on that data flow task. Also note the variable in the Variables window at the bottom, which is configured to change for each file processed by the for each loop.

With this breakpoint enabled, the execution of the package will pause immediately before each invocation of the data flow task. Since that data flow task is inside a for each loop configured to process files in a specified directory, the loop will be paused prior to the processing of each file so we can peek at the variable values that will be used in the data flow.

When this package is executed, I’ll expand the Debug –> Windows menu from the menu bar to show the options available. When the package is running, you’ll see a Locals option within that menu list (note that this option will not be available until the package is executed in SSDT).

When enabled, the Locals window displays a list of variables, parameters, and other metrics. As shown below, the variable we defined for storing each file name is shown along with its current value. Also note that any parameter values will be shown here as well.

With these options set, we can monitor the changing values of that variable during package execution. When I press F5 to continue to the next iteration of the loop (which will again pause before executing the data flow for the next file), we see that the variable value has changed to the next file. Any values that have changed since the last breakpoint will be displayed in red text for easy browsing.

Every time the data flow is executed, we’ll see an updated variable value for the current file name.

Troubleshooting values that can change at runtime presents new challenges in SSIS package design, but using breakpoints along with the Locals window can make this simpler and much more transparent.