Microsoft Business Intelligence

Sunday, 1 May 2016

Breakpoints in SSIS

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.