Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Debugging Control Flow in SQL Server Integration Services - Part 1


By:   |   Read Comments   |   Related Tips: More > Integration Services Development

Problem

How do I debug Control flow logic in SQL Server Integration Services?

Solution

Debugging will help us find and resolve an issue in an SQL Server Integration Services (SSIS) package and we can debug the control flow in three ways:

  • Setting up breakpoints
  • Using Progress tab information
  • Using Debug windows

Setting up breakpoints in SQL Server Integration Services

In SSIS, breakpoints can be enabled by using the Set Breakpoint dialog box. This is achieved by enabling break conditions based on the need.

In the SQL Server Data Tools (SSDT) we can set up two types of break points. The first type enables you to setup the break point for a container, task or package. The second type will allow you to setup the breakpoint for the script task.

Breakpoint for an SSIS package

During execution, the package will be suspended based on the break condition. This will help us to investigate the issue at that point in time. SSIS provides ten break conditions that we can enable on tasks and containers. The breakpoints dialog box will help us set this up.

A breakpoint for a package can be setup as follows. Right click on the package and select "Edit Breakpoints..."

Break Point Menu in SQL Server Data Tools

On the set breakpoint window, choose the relevant events to monitor. In the below picture, breakpoints have been setup for the package for OnPreExecute and OnError events. Click OK to save.

breakpoints have been setup for the package for OnPreExecute and OnError events

Notice the red dot in the control flow (this is an indication of package level breakpoints).

Notice the red dot in the control flow (this is an indication of package level breakpoints)

During execution, the control flow tab will be highlighted with a yellow arrow with a red background. This is an indication that SSDT is waiting for the user to respond. The breakpoint window will list the events available for debugging.

Package Breakpoint During Execution is a red dot with a yellow arrow

Breakpoints can also be modified (Added, Deleted and Modified) when the package is running.

Breakpoint for an SSIS container

In the below example, we have created a "For Loop" container. A SQL task has been added to the "For Loop". The configuration details of the "For Loop" can be found in the picture below.

ForLoop Setup in SQL Server Integration Services

The "For Loop" will be executed for 5 iterations (value of the LoopCount variable - 0,1,2,3 & 4).

Forloop Configuration of 5 iterations

The breakpoint for the for the loop can be set by right clicking on the "For Loop" and editing breakpoints.

ForLoop Breakpoint setup by entering the Edit Breakpoints interface

A special breakpoint condition "Break at the beginning of every iteration of loop" is available for the "For Loop". I have selected this breakpoint condition to break for every iteration of the "For Loop". The hit counter type has been set as "Always" (the default value).

Break at the beginning of every iteration of loop in the Set Breakpoints For Loop

During execution the package will be suspended at the beginning of each iteration. The output window will be updated for each iteration. As this is the first iteration, the SQL task hasnít been executed yet.

Forloop Breakpoint Output

The below picture highlights the messages in the output window for the third iteration.

Forloop Breakpoint After Third Iteration

Now the SQL Task has been executed twice (for Iteration 1 and 2), hence the green indicator is shown on the SQL task.

Progress tab in SQL Server Integration Services

The SSDT designer lists information about tasks and containers in the progress tab. This includes start and finish times, warnings and error messages in order of execution. After the execution of the package, the progress information remains available on the Execution Results tab.

The progress tab will also highlight the status of debugging events. As per the picture below, the package has received a Pre-execute event. After the completion of debugging, the package can be continued (by pressing F5 or using the Continue button).

SQL Server Integration Services Package Breakpoint Progress Window


SSIS Package Breakpoints Completion

Summary

Breakpoints are one of the great debugging tools for SSIS. You will be able to master it by practicing with various examples. There are times they will help you identify issues with a few simple steps.

Next Steps
  • Read more about "How to debug a package" here
  • Read about "Debugging Control Flow" here


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools