Set Breakpoints for SSIS Debugging

Like debugging codes for other languages or tools, it is often necessary to set breakpoints to pause execution so you can examine variable values where you think the problem can be. SSIS provides a very straightforward GUI to help you set breakpoints in SSIS packages.

Where to set breakpoints?

You can set breakpoints on a task or a container. A task can be Execute SQL Task, data flow tasks, script tasks, etc. A container can be a For Loop container, a Foreach Loop container, or a Sequence container.

Moreover, there are eleven break conditions that you can choose from as shown below

These break conditions are defined as:

1. OnPreExecute: Called by a task or a container immediately before it runs.
2. OnPostExecute: Called by a task or a container immediately after it runs.
3. OnError: Called by a task or container when an error occurs.
4. OnWarning: Called when the task is in a state that does not justify an error, but does warrant a warning.
5. OnInformation: Called when the task is required to provide information.
6. OnTaskFailed: Called by the task host when it fails.
7. OnProgress: Called when there is measurable progress about task execution.
8. OnQueryCancel: Called at any time in task processing when a cancel execution is fired.
9. OnVariableValueChanged: Called when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.
10. OnCustomEvent: Called by a custom task-defined events.
11. Loop iterations: Called when the iteration condition in a loop is satisfied. This only appears for a For Loop container and a Foreach Loop container.

Moreover, there are four Hit Count types you can define:

• Always
• Hit count equals
• Hit count greater than or equal to
• Hit count multiple

After you define a Hit Count Type, you can specify a Hit Count at which the breakpoint executes. This is especially useful when you want to skip some iterations and break at some specific iterations.

How to set breakpoints?

In SSIS designer, navigate to the control flow panel.  Right-click the object where you want to set the breakpoint and then click the Edit Breakpoints option. You will see a Set Breakpoints window popup just like the picture shown above.

Next, select break conditions you like to have. Here you can combine multiple break conditions. For example, you can let it break at both OnPreExecute and OnPostExecute events so that you can examine the changes on variables. The default Hit Count Type is grayed out when the related break condition is unchecked. Once a break condition is selected, you can go further to define Hit Count Type and Hit Count. Below is a breakpoint set at a loop when its loop iteration is equal or larger than 2.

After you close the Set Breakpoints window, you will notice a red dot appears on the object with breakpoints.

Furthermore, setting breakpoints in SSIS Script tasks is a little different from the way shown above. If you would like to know more, please stayed tuned.

How to modify breakpoints?

You can modify the breakpoint in the same way you set breakpoints. Right-click the object and then click the Edit Breakpoints option.

Now you have a powerful tool to help your troubleshooting effectively. With breakpoints, you can step through your ETL package to keep track of your variables and status of packages.