Analyze Columns Usage

Overview

The Analyze columns usage functionality allows easy detection and deletion of unused inputs, outputs and columns in a data flow.

Use Cases

Some complex REST configurations result in the generation of many inputs and outputs (e.g. 20 or more) with hundreds of columns in the corresponding REST Source/REST Destination component. The result is a very large package file. Often only a single input or output is used. To delete remaining unused inputs and outputs the Advanced Editor can be used, but it allow deletion only of a single output or a column at a time and is not supported when inputs are more than one.

A more common use case is the removal of unused columns from outputs and inputs. Removing unused output columns can optimize the data flow performance and eliminate the common SSIS warning "Removing this unused output column can increase Data Flow task performance.". The removal of input external columns is useful only if you want to do a further cleanup to decrease the size of the package.

How To Use

To invoke the dialog, select one or many Data Flow tasks, open the context menu and select Analyze columns usage.... The menu item is available also in the data flow surface context menu.

The dialog displays all components, inputs and outputs from the selected Data Flow tasks in a hierarchical fashion.

When an input is selected the input external columns are displayed in a grid.

When an output is selected the output columns are displayed in a grid. If the output has external columns an additional checkbox is displayed to choose if the external columns are deleted along with the output columns.

Used inputs, outputs and columns are displayed in gray color. When a child input or output is used its parent input or output is also considered as used. The same is valid for the input external columns - when a child input external column is used, the parent input external column is considered as used.

Selection

All unused inputs, outputs and columns can be selected by checking the corresponding check box. Initially only the unused inputs and outputs are selected. Used inputs, outputs and columns are displayed in gray and are not available for selection.

When checking a component all its child inputs, outputs and columns are selected if possible. When unchecking all its child inputs, outputs and columns are deselected. If not possible to select all inputs and outputs the component check box displays a mixed state. After the component name is displayed how many inputs and outputs are selected and the total count.

When checking an input or an output all its child columns are selected if possible. When unchecking the child columns are deselected. All columns also can be selected/deselected from the grid header check box. If is not possible to select all columns the input/output and header check box displays a mixed state. The input/output check box displays the mixed state when the input/output is used even when all its columns are selected. After the input/output name is displayed how many columns are selected and the total count.

To select or deselect individual column check or uncheck the column check box in the grid. Grayed columns are used and are not available for selection.

To choose to delete all output external columns in a given output check the Delete external columns check box . The selection is individual for each output.

To select all unused inputs, outputs and columns press the Select unused button or choose Select All from the tree view context menu. To deselect all choose the Deselect All from the tree view context menu.

Deletion

To delete the selected inputs, outputs and columns press the Delete selected button. After a confirmation all selected inputs, outputs and columns are deleted.