Home  >  Blog  >   SSIS

BreakPoints In SSIS

Rating: 4
  1. Share:
SSIS Articles

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.

Learn how to use SSIS, from beginner basics to advanced techniques. Enroll for Free SSIS Training Demo!

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.

Set Breakpoints- Inventory Query Simulator

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:
=>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

Hit Count Multiple

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

Inventory check

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.

Watch 1

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.

MindMajix Youtube Channel

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.

Explore SSIS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!


List of Related Microsoft Certification Courses:

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



Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
SSIS TrainingApr 27 to May 12View Details
SSIS TrainingApr 30 to May 15View Details
SSIS TrainingMay 04 to May 19View Details
SSIS TrainingMay 07 to May 22View Details
Last updated: 03 Apr 2023
About Author

Yamuna Karumuri is a content writer at Mindmajix.com. Her passion lies in writing articles on IT platforms including Machine learning, PowerShell, DevOps, Data Science, Artificial Intelligence, Selenium, MSBI, and so on. You can connect with her via  LinkedIn.

read more
Recommended Courses

1 / 15