Breakpoints in SSIS are very useful to understand the Data Flow at multiple levels. For example, you can use the breakpoints to understand the variable values at the Pre-Execution, Post-Execution, each Iteration etc.
In this article, we will show the step by step approach to configuring the Breakpoints in SSIS with an example. For this demonstration, we are going to use the below shown SSIS package.
I suggest you to refer Execute SQL Task in SSIS Full Row Set to understand the complete package setup and the data that we used.
and the variable that is used for this package are:
and the data that we used for the above package is:
Breakpoints in SSIS Example
In this example, we will show you, How to add Breakpoints to the Foreach Loop Container, and the Data Flow task. In order to add breakpoints on a container, right-click on the Foreach loop container will open the context menu. Please select the Edit Breakpoints.. option from the context menu as we shown below.
Once you select the Edit Breakpoints.. option, a new window called Set Breakpoints will be opened as we shown below.
Following are the list of SSIS Breakpoints, and respective description.
| Break Condition | Description |
|---|---|
| When the Task or Container receives the OnPreExecute event | This event is 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 is 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 is called when an error is occurred by a task or container |
| When the Task or Container receives the OnWarning event | The OnWarning event is 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 event is called when the task is 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. This will help you to cancel the execution at any time. |
| When the Task or Container receives the OnVariableValueChanged event | The OnVariableValueChanged event is called when the variable value changes. In order 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, then you use this OnCustomEvent event. |
For now, we are selecting the Breakpoints on the Preexecute event, and Post Execute event.
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 is hit. |
| Hit Count equals | Execution is suspended when the Hit Count is equal to a number of times breakpoint has occurred. |
| Hit Count Greater than or Equal to | Execution is suspended when the Hit Count is greater than or equal to a number of times breakpoint has occurred. |
| Hit Count Multiple | If you set this option to 4, execution is suspended every fourth time. |
Click OK to close the Breakpoints configuration. From the below screenshot you can see red circle on Foreach loop container.
Let us run the 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 as we shown below.
Now you can that the Watch window is showing only user-defined variables. Let me Hit the continue button
Now you can see that the variable values are replaced with the last inserted row.
Let me remove the breakpoints on Foreach loop container, and add the breakpoints on Data flow task.
Let us run the package. As you can, variables are updated with the first row.
Clicking on the Continue button, variables are updated with the second row
Once the table is inserted, the task will be completed.
Thank You for Visiting Our Blog.
