SSIS data conversion component _ aggregate Conversion

The aggregate conversion function is similar to the aggregate function in SQL statements. It applies the column value and copies the result as a new column to the conversion output. Aggregate conversion supports the following operations

Operation	Description
Group	Divide a dataset into groups. Any data type column can be used for grouping.
Sum	Sums the values in the column. Only columns of the numeric data type can be summed.
Average	Returns the average of the values in the column. You can only calculate the average value for numeric data columns.
Count	The number of items in the returned group.
Count distinct	The number of unique non-null values in the returned group.
Minimum	Returns the minimum value in the group.
Maximum	Returns the maximum value in the group.

To add and configure aggregate conversion, the package must contain at least one data flow task and one data source. Here is a simple example to describe how to configure aggregate conversion. In this example, aggregate the total amount of each order. If you use a T-SQL, it would be a statement:

Select salesorderid, sum (orderqty * unitprice) amount from sales. salesorderdetail

This section describes how to obtain the same result through aggregate conversion.

1. In bids, open the integration services project that contains the required package.
2. Create a package named aggrationdemo In The SSIS package file in Solution Explorer. The following results are displayed:

 

1. Drag and Drop a Data Flow task on the control flow Tab Of The aggregationdemo package,

 

1. In the Connection Manager window, create an oledb connector to connect to the local database adventureworks, and a flat file connector to connect c: \ Users \ zhuyujing \ Desktop \ test.txt.

 

1. On the data flow tab, an ole db source, derived column, aggregate conversion, and target flat file are required. :

 

The configuration of each control is as follows:

• Ole db Source

 

• Derived Column

 

• Aggregate Conversion

 

There are four check boxes in the comparison mark list. Meaning:

Comparison options	Description
Case Insensitive	Specifies whether to compare uppercase/lowercase letters. If this option is set, the Case sensitivity is ignored for string comparison. For example, there is no difference between "ABC" and "ABC.
Ignore the Kana type	Specifies whether to distinguish Japanese from hirakana. If this option is set, the Kana type is ignored for string comparison.
Ignore character width	Specifies whether to distinguish between the single-byte form of a character and the double-byte form of the character. If this option is set, the single-byte format and double-byte format of the same character are considered the same for string comparison.
Ignore non-space characters	Specifies whether to distinguish between space characters and annotation characters. If this option is set, note characters are ignored. For example, "category" is the same as ".

 

• Target flat file

 

After configuring the control task, run the package. The execution result is as follows:

 

Open the target TXT text and check the execution result.