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.


7 comments:

  1. marksonSeptember 14, 2019 at 11:07 AM

    Data Technology-'IT'- is costly. Each CEO, CFO, COO-practically every supervisor at each degree of each organization in presence realizes that certain reality.ExcelR Data Science Courses

    ReplyDelete
  2. James ZicrovOctober 24, 2019 at 5:06 AM

    I think DataViewer is actually a very good tool to learn more about some good aspects of SSIS and PostgreSQL.

    SSIS PostgreSql Write

    ReplyDelete
  3. Repairtech SolutionsDecember 5, 2019 at 11:12 PM

    This is a topic that's near to my heart... Cheers! Where are your contact details though? onsite mobile repair bangalore I absolutely love your blog.. Very nice colors & theme. Did you build this website yourself? Please reply back as I’m wanting to create my own personal site and would like to find out where you got this from or what the theme is called. Thanks!
    asus display repair bangalore I really like it whenever people get together and share opinions. Great blog, keep it up! huawei display repair bangalore

    ReplyDelete
  4. Repairtech SolutionsDecember 5, 2019 at 11:13 PM

    Very good info. Lucky me I discovered your blog by chance (stumbleupon). I have book-marked it for later! online laptop repair center bangalore This website was... how do you say it? Relevant!! Finally I have found something that helped me. Thanks! dell repair center bangalore

    ReplyDelete
  5. Repairtech SolutionsDecember 5, 2019 at 11:14 PM

    Everything is very open with a very clear clarification of the challenges. It was really informative. Your site is very useful. Many thanks for sharing! macbook repair center bangalore I would like to thank you for the efforts you've put in penning this blog. I really hope to check out the same high-grade content by you in the future as well. In fact, your creative writing abilities has motivated me to get my own, personal site now ;) acer repair center bangalore

    ReplyDelete
  6. datasciencecourseJanuary 26, 2020 at 11:28 PM

    Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
    data analytics courses

    data science interview questions

    business analytics courses

    data science course in mumbai

    ReplyDelete
  7. digitaltucrJanuary 27, 2020 at 3:11 AM

    You actually make it look so easy with your performance but I find this matter to be actually something which I think I would never comprehend. It seems too complicated and extremely broad for me. I'm looking forward for your next post, I’ll try to get the hang of it!
    ExcelR Machine Learning Courses
    ExcelR Artificial intelligence course in Mumbai

    ReplyDelete

Subscribe to: Post Comments (Atom)