23 Mar

SSIS: Breakpoints

Breakpoints help to debug code by stopping execution at a specific point, and well, seeing what’s going on.  When I used to write PHP I would use the echo command to print a variable, and then a break, so I could see a variable’s value at that point in the code.  I was debugging in a fairly rudimentary way.  Fortunately, SSIS is a bit more robust.  Well be setting two simple breakpoints so we can see how they work.

Breakpoints are set by clicking on a task in the Control Flow and clicking “Edit Breakpoints”.  There are, depending on the task up to 11 breakpoints and they fire based on the following conditions:

Breakpoint Editor.

Breakpoint Editor.

  1. OnPreExecute: Prior to task execution.
  2. OnPostExecute: After task execution.
  3. OnError: On an error.
  4. OnWarning: On a warning.
  5. OnInformation: Raised during validation and execution when the task returns information.  It’s not errors or warnings.
  6. OnTaskFailed: When the task fails.
  7. OnProgress: When there is measurable progress by the task.
  8. OnQueryCancel: When an executable checks to see if it should stop running.
  9. OnVariableValueChanged: When the value of a variable changes.  It requires the RaisedChangeEvent property be set to true.
  10. OnCustomEvent: When a custom event fires.
  11. LoopIteration: Fires at the beginning of a loop.

To enable a breakpoint, click the checkbox next to it.  There are 4 options you can configure for a checkpoint.

  1. Always: Fires every time the event fires.
  2. Hit Count Equals: Fires when the count equals “Hit Count” value set in the next column.
  3. Hit count greater than or equal to: Fires when the count is equal to or greater than “Hit Count” value set in the next column.
  4. Hit Count Multiple: Breaks on a multiple of this value.  For instance, 5, would break on 5, 10, 15, etc…

Lets configure some breakpoints.

  • Drag a Data Flow to the Control Flow.
  • Right-click the Data Flow and click “Edit Breakpoints”.
  • Place a check in the box next to the OnPreExecute breakpoint and click OK to close the “Set Breakpoints” window.
  • There will be a red circle on the task.

I don't work for Target.

Run the package.  It should stop in the middle of execution.  At the bottom of your screen there should be two windows: Autos and Call Stack.  Feel free to click around in them although they won’t mean much for a package this simple.  Press F5 or the run arrow to finish the package.

Lets do a slightly more complicated breakpoint  Delete the Data Flow task and do the following.

  1. Drag a For Loop Container to the Data Flow.
  2. Right-click the Control Flow and click variables.
  3. Add a new variable, name it intCounter, set its type to Int32, and it’s value to 1.
  4. Double-click the For Loop and configure it as follows.
    1. IntExpression: @intCounter = 0
    2. EvalExpression: @intCounter <= 20
    3. AssignExpression: @intCounter = @intCounter + 1
    4. Click OK to close the Editor.
  5. Right-click the For Loop and select “Edit Breakpoints”.  Configure the “Set Breakpoints” window as follows:
    1. Check the Iteration condition (it’s at the bottom), change “Hit Count Type” to “Hit count multiple” and set the “Hit Count” column to 5.
    2. Click OK to close the editor.
The Watch window.

The Watch window.

Run the package.  While running the package we still need to do a couple of things.

When the first breakpoint fires, do the following.

In the Watch window, lower left on my screen, click in the Name Field and type intCounter.  Do not include the @ symbol.  If you watch this window you should see the variable increment from 4,9,14 to 19.  The reason it’s a 4, and not 5, is that the breakpoint fires at the beginning of the loops iteration and not at the end.

And them’s the, break, golfclap, points.