SQL Server 2005 – SQL Server Integration Services – Part 13

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:

  • Call Stack – when a package is launched, its hierarchical
    structure is reflected by the sequence in which containers and tasks (and
    associated functions) are invoked. Call stack represents this hierarchy, by
    displaying the currently executing component or function (identified by the
    yellow arrow), along with its parent containers (functions). When a new
    component or function is called, debugger adds its name to the list in the Call
    Stack window.

  • Breakpoints – provide a central point for managing existing
    breakpoints (listing their characteristics, such as name, condition, hit count,
    filter, action to take when hit, language, function, file, address, data and
    process) and creating new breakpoints of "Break At Function" type.
    This is done by selecting the appropriate entry from the New menu, which
    results in the display of the New Breakpoint dialog box, where you are prompted
    to provide the name of the function, along with line and character number,
    that, when reached, will result in a breakpoint.

  • Command Window – used for the execution of commands or aliases.
    Since its prompt offers Intellisense capability, you can easily get a review of
    all of the available entries. This includes some convenient shortcuts, such as
    the ability to launch the QuickWatch window (described later in this article)
    by typing two question marks, or any other window described here by typing its
    alias (e.g. locals, callstack, immed, etc.)

  • Immediate Window – intended for evaluating expressions, executing
    statements, and checking variable values.

  • Output – as mentioned before, the information it provides helps
    with monitoring the progress of build and execution actions (in addition to the
    Progress window). This includes error, warning, and informational messages, as
    well as notifications about breakpoints.

  • Watch – supplements Locals windows, by providing the ability to
    add arbitrarily chosen variables, which can also be directly modified via its
    interface (as long as they are read/write).

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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles