A breakpoint is an intentional
stop marked in the code of an application where execution pauses for debugging.
This allows the programmer to inspect the internal state of the application at
that point. When we developing the package in ssis we need to test and
troubleshoot issue. It is helpful to know the status of the data at certain
points in the executing of the package.
In other word we can say that using the
Breakpoints we debug the SSIS package, view the value of the variables. It
enables us to stop a package during the execution and view the status of these
items. We can see the value of variable immediately before or after execution
of the task.
Breakpoints we can set it on the
package or control flow task or container.
Creating Simple package
Here I am taking a simple
example. Taking a For loop container.
Open SSDT.
Take For Loop container.
Creating a variable Count and
setting the value 1.
Now we need to configure the for
Loop properties.
Click ok.
Now I am taking script task in
the For loop container to display the values.
Assign the values
Edit Script
Writing simple code to display the
count value.
Now package is ready to execute.
I am executing the package.
Package executed successfully.
Let’s see how we use Breakpoints
For using the breakpoints we need
to right Click on the package and click on the Edit Breakpoints.
After clicking on the Edit Breakpoints. A
window will be opened.
We need to check the check box
for which event we want to see the value.
Each option in the Set
Breakpoints window will stop the package execution at a different point during
The task:
- OnPreExecute—Just before the task executes
- OnPostExecute—Directly after the task completes
- OnError—When an error occurs in the task
- OnWarning—When a warning occurs in the task
- OnInformation—When the task provides information
- OnTaskFailed—When the task fails
- OnProgress—To update progress on task execution
- OnQueryCancel—When the task can cancel execution
- OnVariableValueChanged—When the value of a variable changes (the RaiseChangedEventProperty of the variable must be set to true)
- OnCustomEvent—When the custom task-defined events occur
- Loop Iteration—At the beginning of each loop cycle
The most commonly used events in breakpoints are OnPreExecute,
OnPostExecute, OnError,
OnWarning and Loop Iteration.
The other properties in the Set Breakpoints window are Hit Count and Hit Count Type. These
properties
Four Hit Count Types exist:
- Always—the breakpoint stops the package every time the breakpoint fires.
- Hit Count Equals—the breakpoint stops the package when the breakpoint fires the number Of times listed in Hit Count.
- Hit Count Greater than or Equal to—the breakpoint stops the package when the breakpoint Reaches the number listed in Hit Count and every time afterwards.
- Hit Count Multiple—the breakpoint stops the package when the breakpoint reaches the number listed in Hit Count and every multiple of the Hit Count number; a Hit Count of 2 stops the package on every other breakpoint event.
Here
I am selecting Break at the beginning of every iteration of the look.
Hit count Type: - as we discuss above you
can select the hit count type and hit count as per your need.
Now I
am executing the package.
For
seeing the result need to open Locals window.
Click
on the Local Window
To keep
continue click on the
We see
the next value
Above
example I show how to use breakpoints on the Container. Similarly we set the
break points on Script task as well as on Control flow and Data flow task.
Setting
Breakpoints on script task.
Setting
Breakpoints on control flow
Click
on the anywhere in the control flow and right click and select the Edit Breakpoints
Select
the breakpoint Condition.
Click
OK.
On
Control flow you see the Red circle.
For
viewing the value on execution time on the Data flow pan we are using Data
viewer.
Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing
ReplyDeleteMsbi Training Online
Msbi Developer Course
great post, thank you.
ReplyDelete