Details of Data Processing:

This package accomplishes the following:

1. Connect to the SQL Server 2005 and access the MyNorthwind (a copy of the Northwind) database.
2. A Data Reader Source will extract data using the following query:

   Select orderID, quantity, UnitPrice from [Order details]

3. A Derived Column data transformation will add a column to the data flow which contains a combination of data from two other columns.
4. A Conditional Split transformation divides the data flow into two flows based on a certain value in the derived column. The rows that satisfies the condition are captured in a recordset destination. The rest of the flow (bad data) gets routed to the next processor, the Aggregate Transformation.
5. The Aggregate transformation orders the data coming into it showing group averages of the derived column.
6. This grouped data then gets into a recordset destination.

Although the final data ends up in recordset destinations, they can also be persisted to other destinations such as Data Reader, OLEDB and File system.

Implementation in the Visual Studio 2005 Designer

Before going into the details of the data processing let us take a look at the building blocks from the VS 2005 Toolbox that can be used to implement it and the interconnections between the building blocks. In addition to the data flow controls three data viewers are also inserted to stop and inspect the data before it goes any further.

Data Extraction

The data is extracted from MyNorthwind database on the SQL Server 2005. While configuring the Data Reader, you make use of a Connection Manager. The details of this Connection Manager is shown in its editor as in the next figure. The connection uses the SqlClient Data Provider. The SQL Server is a default installation with the name [.] and as it is set for SQL Server authentication username and password are required. The database connection is chosen from the drop-down as shown. The connectivity test can be performed and it now allows the data reader to extract data from the database.

The Data Reader is added to the design surface by double clicking the control in the Toolbox. The Data Reader uses the connection manager to access the SQL Server as you see in the next figure in its Connection Manager’s tab.

The Data Reader’s Property shows some of the other configuration details made by the other tabs in the above editor. In particular it shows the query used in extracting the data. It extracts three columns from the Order Details table in the MyNorthwind database and provides this to the next component in the data processing implementation.