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:
- 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.
- Choose OnPreExecute to see values before the task executes and OnPostExecute to see them after execution. Click OK after enabling the breakpoint.
- Run the package in debug mode (press F5) in Visual Studio. The package will run until the breakpoint is hit.
- Once execution stops at the breakpoint, open the Locals window (Ctrl+Alt+V, L or Debug..Windows..Locals)
- 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
This is a useful technique for troubleshooting packages that use variables, particularly if the variable values are changed during package execution.