Next, add an Aggregator transformation to calculate the average, maximum, and minimum prices of items from each manufacturer.
to create an Aggregator transformation.
Select the transformation type to create
dialog box and type AGG_PriceCalculations in the
Enter a new name for this transformation
dialog box. Click
, and then click
The naming convention for Aggregator transformations is AGG_
The Mapping Designer adds an Aggregator transformation to the mapping.
The following image shows a mapping with an unconnected Aggregator transformation:
When you drag ports from one transformation to another, the Designer copies the port description and links the original port to its copy.
If you click
, every port you drag is copied, but not linked.
From the Source Qualifier transformation, drag the PRICE column into the Aggregator transformation.
A copy of the PRICE port now appears in the new Aggregator transformation. The new port has the same name and datatype as the port in the Source Qualifier transformation.
The Aggregator transformation receives data from the PRICE port in the Source Qualifier transformation. You need this information to calculate the maximum, minimum, and average product price for each manufacturer.
Drag the MANUFACTURER_ID port into the Aggregator transformation.
You need another input port, MANUFACTURER_ID, to provide the information for the equivalent of a GROUP BY statement. By adding this second input port, you can define the groups for the aggregate calculation. You will create a group to organize the data by manufacturer.
Double-click the Aggregator transformation, and then click the
column for PRICE.
You want to use this port as an input (I) only, not as an output (O). Later, you use data from PRICE to calculate the average, maximum, and minimum prices.
option for the MANUFACTURER_ID column.
button three times to add three new ports.
When you select the
option for MANUFACTURER_ID, the Integration Service groups all incoming rows by manufacturer ID when it runs the session.
Configure the output ports.
The following table describes the output ports:
You can select each port and click the
buttons to position the output ports after the input ports in the list.