SSIS Debugging - SQL Server 2008

With SSIS, Microsoft provides several debugging and troubleshooting features, including control flow and data flow visual debugging, data viewers, breakpoints, and other debug windows.

Control Flow and Data Flow Visual Debugging

While creating your SSIS package, as you add and configure tasks, containers, or data flow objects, you will see real-time warning icons within them, as shown in Figure. These warning icons contain tooltips that describe which properties are either missing or configured incorrectly.

File System task with a warning icon

File System task with a warning icon

During package debugging, the SSIS Designer also displays task color-coded progress reporting. Without waiting for the SSIS package to finish executing, you can watch the task icons change colors, indicating their current status: gray indicates waiting to run, yellow indicates executing, green indicates success, and red indicates failure.

You can also monitor progress during package execution by viewing the Progress tab (called the Execution tab when the package is not debugging) in the SSIS Designer, as shown in Figure. From here, you can monitor the start and finish times of tasks and containers, as well as view any events, errors, and warnings.

Progress tab in the SSIS Designer

Progress tab in the SSIS Designer

In the SSIS Designer, during package execution all Data Flow paths display the number of rows that have passed through them on the design surface, saving you from needing to validate the actual row counts after the package finishes executing.

Data Viewers

Data viewers allow you to watch data as it moves through a path, enabling you to identify transformation
issues or bugs. You can add a data viewer to the Data Flow design surface by right-clicking the path between two Data Flow components and selecting Data Visualizer. In the Data Flow Path Editor dialog box, add one or more viewers, which allow you to view data in a grid, histogram, scatter plot, or column chart format.

During runtime, a Data Viewer window will appear, as shown in Figure, enabling you to continue moving the data, copy the data to a clipboard, truncate all data from the table, or reconfigure the data viewer.

Data Viewer window during runtime

Data Viewer window during runtime

Breakpoints

The SSIS Designer allows you to set breakpoints (a point in the SSIS package at which the task can be interrupted for manual intervention) within SSIS tasks, containers, or Data Flow components. As shown in Figure, you can configure breakpoints for various events, including OnPreExecute, OnPostExecute, OnError, OnWarning, OnInformation, OnTaskFailed, OnProgress, OnQueryCancel, OnVariableValueChanged, and OnCustomEvent.

Set Breakpoints dialog box

Set Breakpoints dialog box

During package runtime, you can view the Breakpoints window, which lists all enabled breakpoints
within the SSIS package.

Other Debug Windows

In addition to the Breakpoints window, Microsoft includes other windows useful for debugging runtime
of the package:

  • Call Stack: This window shows SSIS objects that are currently running during debug mode.
  • Command: This window can be used for executing commands directly into the BIDS environment (for example, if you wanted to exit debug mode during the execution of an SSIS package, you could type Debug.StopDebugging and press Enter). The Command window uses IntelliSense and allows you to browse through the various IDE commands.
  • Immediate: This window is used for executing commands in order to debug expressions, statements, and variables.
  • Watch: This window allows you to read, edit, and save variables.
  • Output: This window displays status errors related to project compilation and validation.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics