Debugging Data Flows

Objectives

After completing this lesson, you will be able to:

  • Determine the success of a job.
  • Use the interactive debugger.

The View Data Feature

Make sure that your job is successful

The best measure of the success of a job is the state of the target data. Always examine data to be sure that the data movement operation produces the results expected.

  • Data must not be converted to incompatible types or truncated.
  • Data must not be duplicated in the target.
  • Data must not be lost between updates of the target.
  • Generated keys must have been properly incremented.
  • Updated values must have been handled properly.

Using View Data with Sources and Targets

As shown in the following figure, by using the View data feature, it is possible to check the status of data at any point after metadata is imported for a data source. The data is checked before or after data flows are processed. Check the data when jobs are designed and tested to ensure that the design returns the results expected.

Data Details

View data allows you to see source data before a job is executed. Use data details to:

  • Create higher quality job designs
  • Scan and analyze imported table and file data from the Local Object Library
  • View the data for those same objects within existing jobs
  • Refer back to the source data after a job is executed

View data also allows for the checking of target data before a job is executed. Once the job is executed, view the changed data. In a data flow, it is possible to use one or more View data panels to compare data between transforms and within source and target objects.

View data displays data in the rows and columns of a data grid. The path for the selected object displays at the top of the pane.

View Data in a Data Grid

The number of rows displayed is determined by a combination of several conditions:

  • Sample size:

    The number of rows sampled in memory. Default sample size is 1000 rows for imported source, targets, and transforms.

  • Filtering:

    The filtering options that are selected. If the original data set is smaller or if filters are used, the number of returned rows could be less than the default.

View Data Pane

To open a View data pane in a data flow workspace:

  1. In the data flow workspace, select the magnifying glass button on a data flow object.

    A large View Data pane appears beneath the current workspace area.

  2. To compare data, select the magnifying glass button for another object.

    A second pane appears below the workspace area, and the first pane area shrinks to accommodate it.

It is only possible to have two View data windows open at any time. When a third window is selected to be opened, a prompt appears and one of the windows is selected to be closed.

Source and Target Tables

To use View data in source and target tables:

  • On the Datastore tab of the Local Object Library, right-click a table and select View Data from the menu.

  • The View Data dialog box displays.

When both panes are filled and another View Data button is selected, a small menu appears containing window placement icons. The black area in each icon indicates the pane that you want to replace with a new set of data. When a menu option is selected, the data from the latest selected object replaces the data in the corresponding pane.

The Interactive Debugger

The Designer includes an interactive debugger that allows for the troubleshooting of jobs by placing filters and breakpoints on lines in a data flow diagram. The interactive debugger enables the examining and modifying of data, row by row during a debug mode job execution.

Running the job in debug mode and then navigating to the data flow while remaining in debug mode enables you to drill into each step of the data flow and view the data.

When a job is executed in debug mode, the Designer displays several additional windows that make up the interactive debugger: Call Stack , Trace , Variables , and View Data panes, which are shown in the following figure.

View Data Pane

The left View Data pane shows the data in a source table, and the right pane shows the rows that have been passed to the query up to the breakpoint.

Start the Interactive Debugger

  1. In the project area, right-click the job and select Start Debug from the menu.

    The Debug Properties dialog box displays.

  2. Set properties for the execution:

    Specify many of the same properties when executing a job without debugging. In addition, specify the number of rows to sample in the Data Sample Rate field.

  3. Select OK .

    The debug mode begins. While in debug mode, all other Designer features are set to read-only. A Debug icon is visible in the task bar while the debug is in progress.

  4. If you have set breakpoints, in the interactive debugger toolbar, select Get Next Row to move to the next breakpoint.
  5. To exit the debug mode, from the Debug menu, select Stop Debug .

Filters and Breakpoints

It is possible to set filters and breakpoints on lines in a data flow diagram before starting a debugging session that allows for the examining and modifying of data row-by-row during a debug mode job execution. This action is shown in the following figure.

A debug filter functions the same as a simple Query transform with a WHERE clause. Use a filter when reducing a data set in a debug job execution. The debug filter does not support complex expressions.

A breakpoint is the location where a debug job execution pauses and returns control. A breakpoint can be based on a condition, or it can be set to break after a specific number of rows.

Place a filter or breakpoint on the line between a source and a transform or two transforms. If a filter and a breakpoint are set on the same line, Data Services applies the filter first, which means that the breakpoint applies to the filtered rows only.

Set Filters and Breakpoints

  1. In the Data Flow workspace, right-click the line that connects two objects and select Set Filter/Breakpoint from the menu.
  2. In the Breakpoint window in the Column dropdown list, select the column to which the filter or breakpoint applies.
  3. In the Operator dropdown list, select the operator for the expression.
  4. In the Value field, enter the value to complete the expression.

    The condition for filters/breakpoints does not use a delimiter for strings.

  5. If you are using multiple conditions, repeat step 3 to step 5 for all conditions and select the appropriate operator from the Concatenate all conditions using the dropdown list.
  6. Select OK .

Let's Try It

Let me guide you through the use of the debugger filters and breakpoints:

ExerciseStart Exercise

Log in to track your progress & complete quizzes