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.
Now we'll follow the steps below to create a data viewer on an output and see the result.
- Make a copy of the package DataConversion.dtsx and rename it to Sort.dtsx. Then open it and click Data Flow tab.
- Right-click the path between Data Conversion and OLE DB Destination and click "Enable Data Viewer".
- Run the package and the data will be shown up in the data viewer.
- Click Green arrow button to get over the execution.
A magnifier icon is shown up on the path.
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.
Option | Description |
---|---|
Green arrow | Displays the next buffer's data if we still have. |
Detach | Data continues to flow through the path without stops and the data in the viewer will be kept. |
Attach | Displays the data in the current buffer and pause until Green arrow or Detach button is pressed. |
Copy Data | The data in the current buffer is copied to Clipboard. |
This is the editor to set data flow path settings. Let's continue the work.
- Right-click the path again and choose "Edit..." to open the editor.
- In the General tab, click on PathAnnotation and you can choose the items below to show them up on the path or nothing.
- Click Metadata tab, you can see the meta data which flows through the path.
- Click Data Viewer tab, you can see the data viewer is enabled and all the columns are set to be viewed as default.
- Choose "Copy of Book Name" column and click "<" button to move the column to Unused columns. At last, click OK button.
- Run the package again and you will see the column "Copy of Book Name" will not be shown up in the data viewer.
You can click "Copy to Clipboard" to copy the meta data to clipboard.