How to use the Break Points to Validate the Control Flow Tasks and Data Flow in SSIS

Break Points to Validate the Control Flow Tasks and Data Flow in SSIS
The Breakpoints in SSIS are very useful to validate and understand the Control Flow Tasks and the Data Flow at multiple levels.

We can use the Breakpoints in Control Flow at a specific task on the Pre-Execution, Post-Execution of that task, to validate the outcomes of the previously executed tasks.

For Instance, we can use the Breakpoints at ForEach Loop container(used to loop through the files in a Folder using the Variables) to understand how the variable values are changing on the Pre-Execution, Post-Execution, each Iteration of the Loop.

Now we will discuss in detail about the most commonly used Break Points in SSIS

Example::

Suppose we have a package as follows, where are performing the below Tasks..

Truncating the Destination Table :

                                                 Truncate Table [dbo].[Tbl_Sample]

Looping through the input files from a Folder:

Variables used in this Package :

Data Flow Task to Load Data from each Input to the Destination Table :

                                                 [dbo].[Tbl_Sample]

Next two Execute SQL Tasks to perform Updates on the Destination :

               Update [dbo].[Tbl_Sample] Set  Region ='Middle_East' Where Region = 'East'
               Update [dbo].[Tbl_Sample] Set  NetSales =5555.75 Where NetSales <= 3000

In this example, we will see 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.

The following are the list of SSIS Breakpoints, and respective description.

Break Condition	Description
Break 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.
Break 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.
Break When the Task or Container receives the OnError event	The OnError event is called when an error is occurred by a task or container
Break When the Task or Container receives the OnWarning event	The OnWarning event is called when a task or container throws a warning.
Break When the Task or Container receives the OnInformation event	we can use this OnInformation event, when a container or task has to provide any information
Break When the Task or Container receives the OnTaskFailed event	This event is called when the task is failed
Break When the Task or Container receives the OnProgress event	This event is called to update the task execution progress.
Break When the Task or Container receives the OnQuerycancel event	While processing the task, we call this event at any time. This will help we to cancel the execution at any time.
Break 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, we have to set the RaiseChangeEvent of the variable to TRUE.
Break When the Task or Container receives the OnCustomEvent event	If we want to raise any task defined events, then we use this OnCustomEvent event.

For now, we are selecting the two Breakpoints, one is OnPreExecute event on ForEach Loop Container, and another is OnPostExecute event on Data flow task.

OnPreExecute Event :
This Breakpoint will temporarily stop the flow when it reaches to the ForEach Loop Container, before it starts executing (but after finishing the previous Truncate Table task).

OnPostExecute Event :

This Breakpoint will stop the flow after finishing the Dataflow Task. Since this task is inside the ForEach Loop Container, after the finish of each iteration the flow will break.

The 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.

Once we setup the Breakpoints on the desired tasks, we can say OK and run the package.

Now we can see the Package execution Flow will stop/break at the Breakpoint.

Now we can check/validate the records in the Destination table before looping through the inputs and loading them to that table.

Select Count(*) As RecordCount From [dbo].[Tbl_Sample]  

RecordCount
0        

Next click Continue to resume the process flow, so that the FileName variable will pick a value from ForEach loop container, then the flow will reach to the Data Flow Task. Now the data flow task will finish its first iteration, then again it stop due to OnPostExecute event Breakpoint on it. 

Now we can check the values of the FileName variable from the Locals window. Also from Call Stack window we can see the Task of the Package, and at which task the Flow was currently had a Break, and the Breakpoints used in the Package are showin in the CallStack > Breakpoints window.

In the Locals >Variables, we can see so many System variables along with the User defined variables. If we wants to Focus(keep an eye to watch) on a specific variables(eg: FileName). We can add a Watch on those variables by selecting the required variables and right-click on them will open the context menu, then select the Add Watch option as we shown below.

Now the selected Variables will be add to Watch as follows, so that is easy to monitor the values change.

Finally we will click Continue at each Break after finishing our validation to complete the flow.

Now we can check final records Count in the Destination table.

Select Count(*) As RecordCount From [dbo].[Tbl_Sample]

RecordCount
16

Removing Break Points :

We can remove each Breakpoint individually by going through the Edit Breakpoints and de-select the Breakpoint that previously added.

Also, we can remove all the Breakpoints at a time from DEBUG Tab>Delete All Breakpoints.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------