BreakPoints In SSIS

Many programmers use breakpoints to debug programs, viewing the value of variables and following the flow of the logic as they step through the source code. SSIS enables you to set breakpoints on the package or any Control Flow Task or Container. You can also set breakpoints in Script Task code just like most programming environments.

Using the Inventory Example package created in the previous section, follow these steps to enable and use breakpoints.

1. Right-click the Inventory Query Simulator For Loop Container, and choose Edit Breakpoints from the pop-up menu. The Set Breakpoints dialog opens, displaying a list of possible events at which you can set a breakpoint, as shown in below screenshot.

2. Enable the last item, “Break at the beginning of every iteration of the loop,” which is available only for looping containers. Hit Count Type has the following options:
=>Always
=>Hit Count Equals
=>Hit Count Greater Than or Equal To
=>Hit Count Multiple.
The last item will suspend execution when the hit count is equal to a multiple of the value set for Hit Count. For example, setting the Hit Count Type to Hit Count Multiple and the Hit Count to 5 will cause the execution to be suspended every fifth time through the loop.

 

Check Out SSIS Tutorials

 

3. Go ahead and set the type to Hit Count Multiple and the Hit Count to 5, as in below screenshot

4. Click OK. The container will now have a red circle in its top-right corner specifying that a breakpoint has been set (see below screenshot, though you can’t see the colour of the circle in the figure here).

5. Run the package. When the Hit Count reaches 5, the execution will stop and the red dot will change to a red circle with an arrow. You can now view the values of the variables in the Locals window. If the Locals window is not visible, select it by choosing Debug ⇒ Windows ⇒ Locals. Expand Variables, and look for the User variables that were set up for the package (see below screenshot). User::Count should have a value of 5. If the value of a variable cannot be completely viewed in the window, such as a long string, you can mouse over it to see the entire value in a tooltip.

6. Restart the package, and it will run until the Hit Count reaches 10.
There are also watch windows to make it easier to view the variables you are interested in viewing. Open the Locals watch window by selecting Debug ⇒ Windows ⇒ Watch ⇒ WatchLocals. In the first row of the Watch 1 window, type User::Count in the Name field, as shown in below screenshot. You can view the values of all system and user variables from the Locals window. You can also add variables to the watch windows by right-clicking the variables you want to add in the Locals window and choosing Add Watch.

Another very helpful feature is the capability to change the value of variables on the fly. In the watch window, expand User::Count. Right-click and choose Edit Value. Change the value to 40 and restart the package. The next time the execution is suspended, the value should be at 45.

 

Frequently Asked SSIS Interview Questions & Answers

 

The value of some system variables may also be interesting to view and change. For example, you might modify the value of a task property as the package executes. You can use breakpoints and the watch windows to view the value to confirm it is what you expected or to change the value manually to correct it.

An additional debugging window known as the Call Stack window may also help troubleshoot packages. This window shows a list of the tasks that have executed up to the breakpoint. This can be very useful when trying to figure out a very complex workflow.

The capability to set breakpoints on the tasks and containers will save you a lot of time while troubleshooting your packages.

NOTE Data Viewers are similar to breakpoints, but they are used to view data as the package executes. See in The Data Flow Topic for more information on how you can use Data Viewers in your packages.

 

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator