By Ken Whiteside

DataXperts

Using the SSIS Debugger.

Most of the easy wizard and even intermediate SSIS packages you make will just work and be easy to troubleshoot. When you get into using variables, expressions, and some of the fancy toolbox widgets, you will need to see what is happening at runtime.

The Debugger they included is very nice. You can conditionally stop anywhere you want and what you need one way or another.

I’m going to use the Foreach container because it loops and changes variables during the loop. Let’s see what the variables look like during each iteration with the debugger.

Right click the Foreach Loop Container and select “Edit Breakpoints”. I use the PreExecute, PostExecute, and Progress breaks the most.

PreExecute will stop right when the control hits the object before it does anything.

PostExecute will stop as it leaves the object is ready to move to the next one.

OnProgress will stop as progress is logged. Good for Data Flows.

Select a couple breakpoints for the container. Then do the same for the script task.

You must run in Debug mode to actually stop at the breakpoints. You can’t just right click and select “Execute object”.

Press F5, or click “Start Debugging” as shown above to start debugging. If you have other objects in the current task you do not want to fire off, select them and disable them.

The debugger will stop on the first PreExecute you set. Notice the Output Line and that the Variables window is now available. Expand the Variables and get the one the container will be updating on the screen. Then Click the Green arrow that says continue.

Once you cycle through the VBScript task, look again at the Locals window with all the Variables. You’ll notice in the screen shot below the Variable is populated and will change with each iteration of the Foreach container.

You can also change the BreakPoint settings. Click on the Breakpoints window (during debug). Right click on one of the conditions you are using such as PostExecute. We will tell it to display something “When Hit”.

As you see above we want to display the name of the file being processed during each iteration of the Foreach Loop Container.

The message will be displayed in the Output windows. Click on the Output windows and press the continue debugging button until you cycle through the VBScript task again.

The message should show as it does in the above screen shot.

You can also put conditions on the break point so you don’t have to click continue all day long. Maybe you are waiting for an import to show a certain value and then you want to stop and have a look around.

I’m sure I just scratched the surface, but even with this simple usage, the debugger is very valuable.