SQL Server 2005 - SQL Server Integration Services - Part 13
November 28, 2005
Continuing our discussion about monitoring execution of SQL Server 2005 Integration Services packages, which we initiated in our previous article by reviewing logging functionality, we will now turn our attention to debugging and its considerably more sophisticated capabilities. While we will focus on aspects specific to SSIS, equivalent methods (in particular, the ones described in the section dedicated to the Script Task component, later in this article) are applicable to any type of SQL Server projects (for example, Common Language Runtime methods in the form of SQL Server 2005 stored procedures or user-defined functions) developed using Visual Studio 2005 and can be accomplished either via graphical interface of SQL Server Explorer or via breakpoints in .NET modules. We will cover debugging of packages in the context of Control Flow, Data Flow, and code enclosed within Script tasks (within Control Flow) as well as Script components (within Data Flow), using features built into the SSIS Designer and Microsoft Visual Studio for Applications (respectively) in SQL Server 2005 Business Intelligence Development Studio.
In general, debugging involves tracking the execution of a package and its components in real time in order to extract information about the intermediate execution status and values of data being processed. This is accomplished using such means as, for example, color-coded indicators of component status, progress notifications or interactive windows, which provide the ability to query current values of data items, variables and properties, when execution is paused at arbitrarily chosen intermediate points along the package-processing path. The primary purpose of debugging is typically troubleshooting run-time problems, which are caused by logic or data errors (and therefore not detectable through syntax checks).
Some of the information that can be used for debugging is readily available in both Control Flow and Data Flow areas of the SSIS Designer window of the SQL Server 2005 Business Intelligence Development Studio. The source of such information, which is the easiest one to spot and interpret, is the background color of rectangles representing tasks and containers, changing as the corresponding components pass through each of the execution stages. The four possible colors that each task or container can take on (once the package has been launched) are white (indicating that the component is idle, waiting to be invoked), yellow (designating the running phase), green (marking successful completion), or red (signaling that errors were encountered during execution). Any components that have been disabled are displayed in gray (and their color, obviously, does not change since they do not participate in the execution process). More details regarding the component status (such as all tasks and components involved, error or warning descriptions, or start and finish time of individual execution stages, as well as percentages of completion, in the case of data flow processing) are included on the Progress pane of the Package window (which is automatically renamed to Execution Results as soon as the execution is completed), and also appears in the Output window (which you can activate from the Windows submenu of the Debug menu of the SSIS Designer interface).
One of the differences between the Progress pane and Output window is inclusion in the later notifications about breakpoints, which provide another popular (also considerably more powerful) method of obtaining debugging information. They have been commonly used by generations of developers to locate and fix programming errors (and have been incorporated into practically every professional development toolset, including Microsoft Visual Studio). In the case of SQL Server 2005 SSIS packages, breakpoints mark positions within the Control Flow (of a package or an event handler) where the execution process is temporarily paused. Since the placement of breakpoints is arbitrary (within the confines of executable components, such as tasks and containers), they offer the ability to suspend all activities at the desired stage, in order to perform testing of the current execution status, including monitoring variables, testing component properties, or running data queries.
When operating within the SSIS Designer interface, the exact location of a breakpoint is determined by an event associated with it (this can be further extended in the case of Script tasks, which we will demonstrate later in this article). This mechanism should be already familiar to you (as long as you have been following our series of articles), since it is equivalent to the way package logging is configured. More specifically, you have the ability to selectively enable breakpoints to coincide with events associated with any task or container, whose execution you intend to monitor, such as OnPreExecute, OnPostExecute, OnError, OnWarning, OnInformation, OnTaskFailed, OnProgress, OnQueryCancel, OnVariableValueChanged, or OnCustomEvent. Note that the actual list depends on the task or container type - for example, in the case of containers, you also have the ability to break at the beginning of every iteration of the loop (for more information on this topic, refer to our article on SSIS Events and Event Handlers.
Breakpoints are configured from the Set Breakpoints window. The way you activate it depends on whether you want to assign breakpoints to a package-level event or whether you want to accomplish the same for individual components. In the case of the former, you need to click on the Edit Breakpoints item in the context sensitive menu on the empty area of the Control Flow tab in the Designer interface, the latter requires that you choose Edit Breakpoints item from the context sensitive menu of the component you intend to debug. The Set Breakpoints window lists available Break Conditions, which can be selectively enabled (using checkboxes in the Enabled column). For each entry, there are four Hit Count Type options, which determine how frequently events will result in the breakpoint. The value of Hit Count Type set to Always (default) triggers a breakpoint every time the corresponding event takes place. With "Hit count equals" selected, a breakpoint will occur only once, when the number of events reaches the level specified by the Hit Count column (within the Set Breakpoints dialog box). "Hit count greater than or equal to" uses the same Hit Count column to determine the threshold after which subsequent events will result in a breakpoint, and "Hit count multiple" invokes a breakpoint every time a multiple of the value assigned to Hit Count for the selected event is reached (the last three are particularly useful when troubleshooting loop containers). Existence of a breakpoint is indicated by a round circle located either directly on the Control Flow tab or within the rectangle representing a task or container (depending on whether breakpoint has been enabled on the package or a component level). Note that all of the information provided above also applies to assigning breakpoints to Event Handlers.
Once breakpoints are enabled and a package is launched, its execution is suspended whenever a breakpoint is reached (this is reflected by the descriptive message appearing in the Debug results of the Output window). At this point, you can perform a number of actions that could provide additional insight into the current status of execution, such as reviewing properties of the package and its components, enabling addition breakpoints, or testing values of variables and processed data. Relevant information can be extracted using various panes of the Output window, accessible via the following tabs:
In addition to the debug-related panes described above, you also have the available Locals window, containing all user and system variables within the scope of the currently executing component (as well as ExecutionDuration, ExecuteStatus, and ExecutionResult values). The QuickWatch window, available from the Debug menu (or, as mentioned earlier, from the Command Window by typing two question marks at the prompt) is useful for a quick lookup of values for variables, which have not been added to the Watch window or for evaluating expressions at the current breakpoint.
Once you are satisfied with the results, you can continue with the execution (either by pressing the F5 key, clicking on the Continue toolbar button in the Debug toolbar, or selecting the Continue item from the Debug menu) or stop debugging altogether (by pressing the Shift + F5 key combination or choosing the appropriate item from the Debug menu).
While the rules described above apply to all Control Flow tasks and containers, there is a separate debugging procedure that is applicable exclusively to the Script task. In addition to the event-based breakpoints that can be used to suspend execution of this component, it is also possible to create breakpoints directly in the Script task code, using the Microsoft Visual Studio for Applications interface. (To display it, choose the Edit item from the context sensitive menu of the Script task - once the Script Task Editor window appears, select the Script entry on the left hand side, and click on the Design Script command button that appears at the right bottom corner). This is done by positioning the cursor at the relevant line and either pressing the F9 key or selecting the Toggle Breakpoint option from the Debug menu (as you might expect, the same menu item is used to remove an existing breakpoint). A small circle to the left of the line you chose serves as the graphical indication of the change. The already familiar dark red dot (identical to the ones created for event-based breakpoints) will appear within the rectangle representing the Script task (once you return to the Designer interface of the SQL Server 2005 Business Intelligence Development Studio). If you right click on the task and select the Edit Breakpoints... item from its context sensitive menu, you will also notice that the Set Breakpoints dialog box contains an additional entry, which specifies the function where the breakpoint has been set, as well as the line and character numbers, which further narrow down its location. Once you launch the package, the new breakpoint is displayed in the Breakpoints window, along with the information regarding its function, line, and character location, as well as the component GUID. As soon as the breakpoint is reached, debugger launches the Visual Studio for Applications window, with the line containing the breakpoint highlighted and marked by a dark red dot with a yellow arrow. This allows you to interact with the execution process in a manner similar to the one implemented for event-based breakpoints. In this case, the Debug menu offers Step Into, Step Over, and Step Out commands, allowing you to work with more complex, nested functions and loops. You also have the ability to launch the QuickWatch window to check values of expressions and variables or add variables to the Watch window (note, however, that you have access to script variables only, rather than the ones defined in the SSIS Designer).
In the next article of our series, we will look into debugging of the Data Flow task and its components, as well as other issues relating to debugging - in particular, its impact on package performance.