Debugging Data Flow
Microsoft SQL Server 2005 Integration Services (SSIS) and the SSIS Designer include features and tools that you can use to troubleshoot the data flows in an Integration Services package.
- SSIS Designer provides data viewers.
- SSIS Designer and Integration Services transformations provide row counts.
- SSIS Designer provides progress reporting at run time.
Data Viewers
Data viewers display data between two components in a data flow. Data viewers can display data when the data is extracted from a data source and first enters a data flow, before and after a transformation updates the data, and before the data is loaded into its destination.
To view the data, you attach data viewers to the path that connects two data flow components. The ability to view data between data flow components makes it easier to identify unexpected data values, view the way a transformation changes column values, and discover the reason that a transformation fails. For example, you may find that a lookup in a reference table fails, and to correct this you may want to add a transformation that provides default data for blank columns.
A data viewer can display data in a grid, histogram, scatter plot, or column chart.
- Using a grid, you select the columns to display. The values for the selected columns display in a tabular format.
- Using a histogram, you select the column to model in the histogram. A histogram shows the distribution of numeric values and works only with numeric data.
- Using a scatter plot, you select the columns to appear on the x-axis and y-axis. The scatter plot graph works only with numeric data.
- Using a chart format, you select the column to model with the column chart. The column chart displays the occurrence count of discrete values in the selected column.
You can also include multiple data viewers on a path. You can display the same data in different formats—for example, create a chart view and a grid view of the data—or create different data viewers for different columns of data.
When you add a data viewer to a path, SSIS Designer adds a data viewer icon to the design surface of the Data Flow tab, next to the path. Transformations that can have multiple outputs, such as the Conditional Split transformation, can include a data viewer on each path.
At run time, a Data Viewer window opens and displays the information specified by the data viewer format. For example, a data viewer that uses the grid format shows data for the selected columns, the number of output rows passed to the data flow component, and the number of rows displayed. The information displays buffer by buffer and, depending on the width of the rows in the data flow, a buffer may contain more or fewer rows.
In the Data Viewer dialog box, you can copy the data to the Clipboard, clear all data from the table, reconfigure the data viewer, resume the flow of data, and detach or attach the data viewer.
To add a data viewer
Row Counts
The number of rows that have passed through a path is displayed on the design surface of the Data Flow tab in SSIS Designer next to the path. The number is updated periodically while the data moves through the path.
You can also add a Row Count transformation to the data flow to capture the final row count in a variable. For more information, see Row Count Transformation.
Progress Reporting
When you run a package, SSIS Designer depicts progress on the design surface of the Data Flow tab by displaying each data flow component in a color that indicates status. When each component starts to perform its work, it changes from no color to yellow, and when it finishes successfully, it changes to green. Red indicates that the component failed.
The following table describes the color-coding.
| Color | Description |
|---|---|
No color |
Waiting to be called by the data flow engine. |
Yellow |
Performing a transformation, extracting data, or loading data. |
Green |
Ran successfully. |
Red |
Ran with errors. |