微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

时间:2023-03-09 02:38:47
微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

开篇介绍

上午在天善回答看到这个问题 - SSIS 导出数据文件,能否在第一列增加一个行号,很快就帮助解决了,方法就是在 SQL 查询的时候加一个 ROW_NUMBER() 就可以了。

后来想起在两年前我的第一个 BI 项目上也有类似的文件输出需求,但是比这个要复杂的多,因为涉及到多个输入列的逻辑判断和计算问题,比如还有一些 XML 格式的数据。这些逻辑不能直接在 SQL 查询时直接实现,比如还要做 XML 格式验证,字符串匹配查找,还有其它的比较复杂的逻辑。所以就选择了另外一种方式 - 在数据源输入和文件输出组件之间加了一个 Script Component 组件,利用它程序化处理添加了必要的输出列供下游使用,当然逻辑比较复杂在 Script Component 中写了不少代码还有异常记录输出等。

虽然没有必要使用 Script Component 来解决这个问题,但是还是可以通过这个小例子简单介绍一下 Script Component,多了解一种方法,在项目需要的时候就多一种选择。

使用 Script Component 为数据流输出添加行号

数据源可以随便选用一张表来进行测试输出。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

数据源向下的输出列 -

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

拖放一个排序控件,如果希望输出的格式按某列排序的话,可以选择一个列来排序,这里使用 FullName 进行排序。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

拖放一个 Script Component,这里要选择 Transfomation 转换,意味着在输入源和输出目的地之间进行中间转换的组件。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

如果以后在 Script 中有比较复杂的逻辑运算需要使用到输入源的某些列的话,可以在这里选中它。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

最重要的就是这里的输出列了,这个输出列之前并不存在,是通过 Script Component 创建出来的,后面可以使用到的。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

选择 Script 项并点击 Edit Script 打开之后就能看到这些代码,这里面有几个方法。

  • PreExecute() - 只会被调用一次,在数据流执行到这个控件的时候调用,并且是在所有行处理前调用,可以在这里做一些初始化计算。
  • PostExecute() - 当所有行全部处理完之后调用,可以在这里和 PreExecute() 配合起来记录处理的时间,时长等等。
/// <summary>
/// This method is called once, before rows begin to be processed in the data flow.
///
/// You can remove this method if you don't need to do anything here.
/// </summary>
public override void PreExecute()
{
base.PreExecute();
/*
* Add your code here
*/
} /// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
base.PostExecute();
/*
* Add your code here
*/
} /// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
/// string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
/// Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
* Add your code here
*/
}

最后的一个方法是我们要使用到的,就是转换处理。在方法外面定义一个变量,表示行号从 1 开始。方法参数 Row 已经将之前在 Script Component 中引用的 InputColumn 和 创建的 OutputColumn 全部包装成了属性,直接可以通过 Input0Buffer Row 来调用。 那么每进来一行数据,这个方法就被调用一次并同时向下输出一次。也就是说进来一行,处理一行,出去一行。

    int rowNumber = ;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.RowNumber = rowNumber;
rowNumber = rowNumber + ;
}

保存并执行包,开启一个 Data Viewer 查看一下运行时的数据,就能看到 RowNumber 已经创建好了,下游就可以向文件写入数据了。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

使用 ROW_NUMBER()来实现添加数据流的行号

当然对于这个例子,直接使用 ROW_NUMBER() 最简单了,维护成本很低。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

一步就搞定了同样的效果。

微软BI 之SSIS 系列 - 通过 ROW_NUMBER 或 Script Component 为数据流输出添加行号的方法

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server) 如果觉得这篇文章看了对您有帮助,请帮助推荐,以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。