SSIS - How to Use Data Viewer in SSIS Package

Scenario:

Let’s say we are developing a package and it extracts some records from source, Implement some business logic by using different transformations and finally load into destination (table/file). When we look at destination, record is incorrect but we are not sure what happen to source record. We want to see the change in record/records after each of transformation to find out which logic is not working correctly.

Solution:

SQL Server Integration Services (SSIS) provided Data Viewer in Data Flow Task. Data Viewer can be used between two transformations to see the data. When we executes our package Data Viewer pop up window shows data so we can see What is changed from Input to Output.

In this example we are extracting few records from Source, We want to see what we are extracting. We have used aggregate transformation that is grouping by CountryName and doing Sum operation on SaleAmount. We can create second data viewer after Aggregate transformation to see the data.
To use Data Viewer between Transformations, Double click on green connection that exists between two transformations, it will open Data Viewer Editor (Data Flow Path Editor).

There are three options on Left Pane
General: Provides general information
MetaData : Provided meta data information of columns
Data Viewer: This is the tab where we will be able to select the column those we want to include in Data View.

In SSIS 2008/ R2 and previous versions where other options were available in Data Viewer, those options are removed. The only Grid option is left in SSIS 2012 and latest versions and that is even not called Grid anymore but only Data Viewer.

Data Viewer Configuration window in SSIS 2008R2 and old

SSIS 2012  and latest versions Data Viewer Editor

Data Viewer Editor Window with only Data Viewer Tab in SSIS 2012 and Latest versions

Once data viewers are created, we can execute our package. We will be able to see data at different stages of execution.

How to use Data Viewer in SSIS Package to view data while debugging SSIS Package

We can hit Play button in Data Viewer Output window to go to next Data Viewer. The data can also be copied from Data Viewer and used for testing.