SSIS Data Viewer

A data viewer is used to display data between two data flow transformations in SSIS packages.

Before creating the package, let's change the content of the source Excel file C:\SSIS\Books.xlsx as follows.

[Excel Data Books]

Now we'll follow the steps below to create a data viewer on an output and see the result.

  1. Make a copy of the package DataConversion.dtsx and rename it to Sort.dtsx. Then open it and click Data Flow tab.

  2. Right-click the path between Data Conversion and OLE DB Destination and click "Enable Data Viewer".
  3. [Enable Data Viewer]

    A magnifier icon is shown up on the path.

    [Data Flow With Data Viewer]

  4. Run the package and the data will be shown up in the data viewer.
  5. Data Shown up in Data Viewer

    In Data Flow task, data is transferred buffer by buffer. That means the data will be saved to a buffer before going to the next transformation. The buffer exists in memory or disk with limited size. The buffer size can be set in the property of the task. Now we can explain the above buttons in the data viewer.

    Green arrowDisplays the next buffer's data if we still have.
    DetachData continues to flow through the path without stops and the data in the viewer will be kept.
    AttachDisplays the data in the current buffer and pause until Green arrow or Detach button is pressed.
    Copy DataThe data in the current buffer is copied to Clipboard.

  6. Click Green arrow button to get over the execution.

This is the editor to set data flow path settings. Let's continue the work.

  1. Right-click the path again and choose "Edit..." to open the editor.

  2. In the General tab, click on PathAnnotation and you can choose the items below to show them up on the path or nothing.
  3. [Path Annotation]

  4. Click Metadata tab, you can see the meta data which flows through the path.
  5. [Meta data On Path]

    You can click "Copy to Clipboard" to copy the meta data to clipboard.

  6. Click Data Viewer tab, you can see the data viewer is enabled and all the columns are set to be viewed as default.

  7. Choose "Copy of Book Name" column and click "<" button to move the column to Unused columns. At last, click OK button.
  8. Data View Setting

  9. Run the package again and you will see the column "Copy of Book Name" will not be shown up in the data viewer.