如何通过分组和计数在 SSIS 中获得所需的输出? [英] How to get desired output in SSIS by grouping by and count?
问题描述
我正在创建一个 SSIS 包,如果一个产品标签存在重复的产品代码,则需要通知用户.我们通过在共享位置发送给我们的 csv 平面文件检索产品.
I'm creating an SSIS package wherein a user needs to be notified if there is a duplicate productcode for one productlabel. We retrieve the products thru a csv flat file being sent to us in a shared location.
在这个例子中,我有 productlabel Physio Ormix 和 Nixen 的 Productcode = 1a.
In this example I have Productcode = 1a for both productlabel Physio Ormix and Nixen.
所以基本上 productcode = 1a 是重复的,因为它也被 productlabel=Nixen 使用.因此,通知用户使用重复的 ProductCode 和 Productlabel.我尝试创建一个按 ProductCode 执行分组并对其进行计数的聚合.
So basically the productcode = 1a was a duplicate because it was also used by the productlabel=Nixen. Hence, notify users for the duplicate ProductCode and Productlabel used. I tried creating an aggregate that performs group by ProductCode and Counts it.
有人能给我一些关于如何做到这一点的提示吗?
Can someone give me tips on how to do this?
推荐答案
我认为您可以使用脚本组件和条件拆分来获取重复项,而无需所有这些逻辑:
I think you can use a script component and a conditional split to get the duplicates without all this logic:
- 在数据流任务中添加一个脚本组件
- 添加类型为
DT_BOOL
的输出列(示例名称为Flag
) 在脚本组件内部写一个类似的脚本:
- Inside the data flow task add a Script Component
- Add an output column of type
DT_BOOL
(example name isFlag
) Inside the script component write a similar script:
using System.Collections.Generic;
public class ScriptMain:
UserComponent
{
List<string> lstKey = new List<string>;
List<string> lstKeylabel = new List<string>;
public override void Input0_ProcessInputRow(InputBuffer0 Row)
{
if(!lstKey.Contains(Row.ProductCode){
lstKey.Add(Row.ProductCode);
lstKeylabel.Add(Row.ProductCode + ";" + Row.ProductLabel);
Row.Flag = true;
}else if(lstKeylabel.Contains(Row.ProductCode + ";" + Row.ProductLabel)) {
Row.Flag = true;
}else{
Row.Flag = false;
}
}
}
在脚本组件后添加条件分割,表达式类似:
Add a conditional split after the script component with a similar expression:
[Flag] == true
通过真路径传递的所有记录都是唯一的,在假路径中传递的所有行都是重复的.
All records that are passed thru the true path are unique, all rows passed in the false path are the duplicates.
这篇关于如何通过分组和计数在 SSIS 中获得所需的输出?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!