Breakpoints in SSIS

Breakpoints in SSIS are very useful to understand the Data Flow at multiple levels. For example, you can use the SSIS breakpoints to understand the variable values at the Pre-Execution, Post-Execution, each Iteration, etc.

Let us see the step by step approach to configuring the Breakpoints in SSIS with an example. For this Breakpoints demonstration, we are going to use the below shown package.

I suggest you to refer Execute SQL Task in Full Row Set to understand the complete package setup and the data that we used.

And the variable used for this SSIS Breakpoints package are:

and the data that we used for the above package is:

Breakpoints in SSIS Example

In this example, we show you how to add Breakpoints to the Foreach Loop Container and the Data Flow task. To add SSIS breakpoints on a container, right-click on the Foreach loop container will open the context menu. Please select the Edit Breakpoints.. option from it.

Once you select the Edit Breakpoints.. option, a new window called Set Breakpoints will open.

The following are the list of SSIS Breakpoints and respective descriptions.

Break Condition	Description
When the Task or Container receives the OnPreExecute event	This event raised or called when a task is about to execute. It is useful to watch the variables of the task or a container before it runs.
When the Task or Container receives the OnPostExecute event	This event raised or called immediately after the task is completed or executed. The OnPostExecute event is useful to watch the task or a container variable after the task finishes.
When the Task or Container receives the OnError event	The OnError event called when a task or container occur an error
When the Task or Container receives the OnWarning event	The OnWarning event called when a task or container throws a warning.
When the Task or Container receives the OnInformation event	You can use this OnInformation event when a container or task has to provide any information
When the Task or Container receives the OnTaskFailed event	This SSIS Breakpoint event is called when the task failed
When the Task or Container receives the OnProgress event	This event is called to update the task execution progress.
When the Task or Container receives the OnQuerycancel event	While processing the task, You call this event at any time. It helps to cancel the execution at any time.
When the Task or Container receives the OnVariableValueChanged event	The OnVariableValueChanged event called when the variable value changes. To raise this event, you have to set the RaiseChangeEvent of the variable to TRUE.
When the Task or Container receives the OnCustomEvent event	If you want to raise any task defined events, use this OnCustomEvent event.

For this SSIS Breakpoints demo, we are selecting the Breakpoints on the Preexecute event, and Post Execute event.

The SSIS Breakpoints allow four different options for the Hit Count type, and they are:

Hit Count type	Description
Always	Execution will be suspended when the breakpoint hit.
Hit Count equals	Execution suspended when the Hit Count is equal to the number of times breakpoint has occurred.
Hit Count Greater than or Equal to	Execution suspended when the Hit Count is greater than or equal to the number of times breakpoint has occurred.
Hit Count Multiple	If you set this option to 4, execution suspended every fourth time.

Click OK to close the Breakpoints configuration. From the below screenshot, you can see the red circle on the Foreach loop container.

Let us run the SSIS Breakpoints package and check the variables flow in the local’s window.

I think there are too many system variables in the local’s window. So, let me select the required variables (user-defined variables), and right-click on them will open the context menu. Please select the Add Watch option.