SSIS 聚合转换

SSIS 中的聚合转换执行两项主要任务

1. 对输入数据应用聚合操作
2. 它允许您根据输入列中的值对数据进行分组。

SSIS 中的聚合转换执行与 SQL 聚合函数 类似的功能。操作包括以下内容

1. GROUP BY：就像 SQL SELECT 查询中的 GROUP BY 子句一样。
2. COUNT：它将计算此列中的值数。如果您选择 (*) 作为输入列，则会将 NULL 值计入在内。否则，将忽略 NULL 值。
3. COUNT DISTINCT：它将计算此列中不同值的数量。
4. SUM：计算列值的总和。
5. AVERAGE：计算列值的平均值。
6. MINIMUM：计算列值的最小值。
7. MAXIMUM：计算列值的最大值。

SSIS 中的聚合转换可以在基本模式或高级模式下配置。在基本模式下，聚合转换有一个输出。在高级模式下，它可以有多个输出，每个输出包含不同聚合的结果。

让我们通过一个示例看看如何配置 SSIS 中的聚合转换以生成单个输出。

提示：有关如何配置聚合转换中的多个输出，请参阅有关 高级模式下的转换的文章。有关更多转换，请单击此处。

IsBig 属性

SSIS 中的聚合转换包含 IsBig 属性，该属性用于输出列以处理大数字或高精度数字。如果任何列值可能超过 40 亿，则应将 IsBig 设置为 1。通过将 IsBig 属性设置为 1，聚合转换将

1. 使用 DT_R8 数据类型而不是 DT_R4 数据类型。
2. 使用 DT_UI8 数据类型来存储 Count 结果。
3. 您可以使用 DT_UI4 数据类型来存储 Distinct count 结果。

SSIS 基本模式聚合转换示例

例如，如果我们想找到每个颜色的销售金额总和，那么我们可以使用此 SSIS 聚合转换。在此示例中，我们将在 AdventureworkDW2014 中存在的 DimGeography 和 FactResellerSales 表上执行所有聚合转换操作。

步骤 1：打开 BIDS，然后将数据流任务从工具箱拖放到控制流中，并将其命名为 Aggregate Transformation。

双击它将打开数据流选项卡。

步骤 2：从 SSIS 工具箱将 OLE DB Source 和 Aggregate Transformation 拖放到数据流区域

步骤 3：在数据流区域双击 OLE DB Source。它将打开 连接管理器 设置并提供编写语句的空间。这里，我们正在使用 AdventureworkDW2014 中存在的 DimGeography 和 [FactResellerSales]

用于 SSIS 聚合转换基本模式检索数据的命令是

USE [AdventureWorksDW2014] 
GO 
SELECT [EnglishCountryRegionName]
      ,[StateProvinceName]
      ,[City]
      ,[PostalCode]
      ,RESELLER.[UnitPrice]
      ,RESELLER.[ProductStandardCost]
      ,RESELLER.[TotalProductCost]
      ,RESELLER.[SalesAmount]
      ,RESELLER.[TaxAmt]
    
FROM [DimGeography]
 INNER JOIN 
     [FactResellerSales] AS RESELLER ON
 [DimGeography].[SalesTerritoryKey] = RESELLER.[SalesTerritoryKey]

步骤 4：单击列选项卡以验证列。在此选项卡中，我们还可以取消选中不需要的列。

将蓝色箭头从 OLE DB Source 拖到 Aggregate Transformation 以连接数据。

SSIS 聚合转换编辑器

步骤 5：双击 SSIS Aggregate Transformation 以打开编辑器。接下来，在下方面板中，我们通过选中可用输入列来选择要执行聚合转换操作的所需列。

一旦选中了所需的列，这些列名将出现在下方面板中的输入列中。

• 输入列：从 OLE DB 源收到的列。
• 输出别名：这与 别名 列相同。在这里，我们可以根据项目要求更改列名。
• 操作：我们上面已经讨论过了。

对于这个 SSIS 聚合转换示例，我们将使用下面的大部分聚合函数。

从上面的 SSIS 聚合转换屏幕截图中，您可以发现我们测量了UnitPrice、ProductStandardCost 的总和、TotalProductCost 的最小值、SalesAmount 的最大值和 TaxAmount 的平均值。并按 EnglishCountryRegion、然后按 StateProvinceName、然后按 City 进行分组。

注意：如果您的输入列是字符串类型，您将看不到 Sum、Average 和其他聚合函数，只有 Group by。在将列的数据类型提供给聚合转换之前，请务必将其转换为适当的数据类型。

单击确定。

步骤 6：将 OLE DB Destination 拖放到数据流区域，并将蓝色箭头从 Aggregate Transformation 拖到 OLE DB Destination

步骤 7：现在，我们需要提供目标的服务器、数据库和表详细信息。因此，双击 OLE DB Destination 并提供所需的信息。

在这里，我们指向了我们的目标数据库。暂时，我使用了 New 按钮为我创建了一个目标表，并将其命名为 Aggregate Transformation Basic Mode。但实际上，您不应该这样设计您的表。

步骤 8：单击 SSIS Aggregate Transformation 映射选项卡以检查源列是否已正确映射到目标列。

单击确定以完成我们的 SSIS 包设计中的 Aggregate Transformation。让我们运行该程序包。

让我们打开 SQL Server Management Studio 并编写以下语句来检查结果。

SELECT [EnglishCountryRegionName]
      ,[StateProvinceName]
      ,[City]
      ,[UnitPrice]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
  
FROM [Aggrigate Transformation Basic Mode]

SSIS 聚合转换高级选项

聚合转换支持多个输出。这意味着聚合转换可以从源读取数据一次。然后，我们可以创建各种输出，每个输出可能具有不同的聚合集。在这里，我们通过一个示例向您展示如何配置 SSIS 高级模式下的聚合转换中的多个输出。

SSIS 中的聚合转换在编辑器的“高级”选项卡中包含一组属性。通过设置这些属性，我们可以提高聚合转换的性能。

Key Scale：在此选项中，指定聚合可以写入的键的大致数量。默认情况下，此选项的值为 Unspecified。

值	描述
Unspecified	未使用 Key Scale 属性。
Low	聚合可以写入近 500,000 个键。
Medium	聚合可以写入近 5,000,000 个键。
High	聚合可能写入超过 25,000,000 个键。

• Keys：指定聚合可以写入的键的确切数量。键是指分组操作预期的分组数量。如果同时设置了 Key Scale 和 Keys 属性，则 Keys 值优先。
• Count Distinct Scale：我们可以指定聚合可以写入的不同值的近似数量。
• Count Distinct Keys：在这里，我们可以指定聚合可以写入的不同值的确切数量。如果同时指定了 CountDistinctScale 和 CountDistinctKeys，则 CountDistinctKeys 具有优先权。
• Auto extend factor：在此 SSIS 聚合转换选项中，我们可以指定它在聚合过程中可以扩展的内存百分比。我们可以选择 1 到 100 的值。默认情况下，此选项的值为 25%。

SSIS 高级模式下的聚合转换示例

步骤 1：打开 BIDS，然后将数据流任务从工具箱拖放到控制流中，并将其重命名为 Aggregate Transformation。

步骤 2：将 OLE DB Source 和 Aggregate Transformation 从工具箱拖放到 SSIS 数据流区域

在数据流区域双击 OLE DB Source 将打开连接管理器设置并提供编写 SQL 语句的空间。这里，我们正在使用 AdventureworkDW2014 中存在的 DimGeography 和 [FactResellerSales]

我们在上面的屏幕截图中使用的 SQL 命令用于检索数据是

SELECT [EnglishCountryRegionName]
      ,[StateProvinceName]
      ,[City]
      ,[PostalCode]
      ,RESELLER.[UnitPrice]
      ,RESELLER.[ProductStandardCost]
      ,RESELLER.[TotalProductCost]
      ,RESELLER.[SalesAmount]
      ,RESELLER.[TaxAmt]  
FROM [DimGeography]
  INNER JOIN 
     [FactResellerSales] AS RESELLER ON
 [DimGeography].[SalesTerritoryKey] = RESELLER.[SalesTerritoryKey]

步骤 4：单击列选项卡以验证列。在此选项卡中，我们还可以取消选中不需要的列。

步骤 5：双击 Aggregate Transformation 以打开编辑器。上半部分有一个按钮可在 SSIS Aggregate Transformation 基本模式和高级模式之间切换。在此示例中，我们希望配置多个输出，因此请选择 Advanced 模式。

单击 Advanced 模式按钮后，将打开一个新窗格来配置多个输出。

• Aggregate Name：指定聚合的名称。
• Group By Columns：这里显示了我们用于分组的列列表。

接下来，在下方面板中，我们通过选中可用输入列来选择要执行聚合转换操作的所需列。

一旦选中了所需的列，这些列名将出现在下方面板中的输入列中，如下图所示。

从上面的屏幕截图中，您可以发现我们将聚合名称指定为 COUNTRY NAME，并将 Group by 指定为 EnglishCountryRegionName。

让我们再添加一个聚合，并将其命名为 STATE NAME，并将 Group by 指定为 EnglishCountryRegionName 和 StateProvinceName。

从上面的屏幕截图中，我们计算了 Unit Price 的总和、Product Standard Cost 的总和、Total Product Cost 的平均值、Sales Amount 的最小值和 Tax Amount 的最大值。并按 English Country Region、然后按 State Province Name 进行分组。

让我们再添加一个聚合，并将其命名为 CITY，并将 Group by 指定为 EnglishCountryRegionName、StateProvinceName 和 City。

从上面，我们计算了 Unit Price 的总和、Product Standard Cost 的平均值、Total Product Cost 的最小值、Sales Amount 的最大值和 Tax Amount 的总和。并按 English Country Region、然后按 State Province Name、然后按 City 进行分组。

步骤 6：将 3 个 OLE DB Destination 从工具箱拖放到数据流区域，以配置从聚合转换获得的三个输出。

我们已将上面的 OLEDB Destination 重命名为 Group By Country、Group By State 和 Group By City。

步骤 7：当您将 Aggregate Transformation 的输出箭头拖到 OLE DB Destination 时，将打开一个 Input Output Selection 窗口以选择一个输出，这里我们选择 COUNTRY NAME。

让我们将 State name 输出分配给第二个 OLE DB Destination。

我们还有一个输出 (CITY) 剩下，所以当我们从 SSIS 聚合转换将绿色箭头拖到第 3 个 OLE DB Destination 时，它会自动分配给 City。

步骤 8：现在，我们需要为 country name 配置 OLE DB Destination。因此，双击 OLE DB Destination 并提供所需的信息。

在这里，我们选择以下数据库作为目标数据库，并选择 [Group By Country] 表作为目标表。

步骤 9：单击映射选项卡以检查聚合源列是否已正确映射到目标列。

步骤 10：现在，我们需要为 group by state 聚合输出配置 OLE DB Destination。因此，双击 OLE DB Destination 并提供所需的信息。

这里，我们选择以下数据库作为目标数据库，并选择 [Group By State] 表作为目标表

重复步骤 9

步骤 12：我们需要为 city 输出行配置 OLE DB Destination。因此，双击 OLE DB Destination 并提供所需的信息。

这里，我们选择 [Group By City] 表作为目标表。

重复步骤 9。

单击确定以完成我们的包设计。让我们运行该程序包

让我们看看 Group By English Country Region Name 的结果。为此，请打开 Management studio 并编写以下查询

SELECT [EnglishCountryRegionName]
      ,[UnitPrice]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
  FROM [GROUP BY COUNTRY]

输出

现在，查看 Group By English Country Region Name 和 State Province Name 的结果。

SELECT [EnglishCountryRegionName]
      ,[StateProvinceName]
      ,[UnitPrice]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
  FROM [GROUP BY STATE]

让我们看看 Group By English Country Region Name、State Province Name 和 City 的结果。

SELECT [EnglishCountryRegionName]
      ,[StateProvinceName]
      ,[City]
      ,[UnitPrice]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
  FROM [GROUP BY CITY]