Tuesday, December 29, 2015

Creating and Executing a SSIS Project Package - Part 3

In the previous part, Part 2 we successfully configured a SSIS Package consisting of a ADO.NET Source and a Recordset Destination. However we did not see the returned rows from the data source although we know it returned 8 rows.

In this post we will add a Data Viewer to monitor the data going from Data Source to Recordset. The data flowing is seen in the Data Viewer which you may copy.

Adding a Data Viewer to the design

Highlight the arrow connecting the ADO.NET Source to Recordset Destination. This is line is called the Data Flow Path.

Data Viewer_01

After right clicking on Data Flow Path, pick Enable Data Viewer by choosing it in the list. It sits in the Data Flow Path as shown.

Data Viewer_02

Right click on an empty spot in the designer and pick Execute Task.

The task is processed and the data flowing out of data source first appears in the Data Viewer. The data is shown in the  ADO.NET Source Output Data Viewer at Data Flow Task window as shown. The data flow is temporarily stopped here.
Data Viewer_03

This is an intermediate point in the data flow and when you click Detach (it changes to Attach) and the task is completed as shown. You might have also noticed that we retrieved only two columns into memory but the data veiwer is showing all the columns.

Data Viewer is a useful device to monitor data flows in SSIS in Microsoft BI.

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...