微软BI 之SSIS 系列 - Execute SQL Task 中的 Single Row 与 Full Result Set 的处理技巧
时间:2022-03-13 23:25
开篇介绍
Execute SQL Task 这个控件在微软BI ETL 项目中使用的频率还是非常高的,也是大部分入门 SSIS 初学者最早接触到的几个控制流控件。 我们通常使用 Execute SQL Task 的场景包含但不止于以下几类:- 在从源端加载数据到 Staging 表之前使用 Execute SQL Task 执行一些 Truncate 操作。
- 执行一些 Log 的插入,更新操作。
- ETL 过程中的 Merge 语句操作。
- XML 的输出处理。
Single Row
首先,Single Row 指的就是在 Execute SQL Task 中返回的就是一个单行的记录,允许多列。 通常在 Execute SQL Task 中使用 Single Row 的时候是为了在控制流中控制流程的流转,满足一定的条件走一个分支流程,不满足则走另外一个流程。 比如,在包执行之初查询数据库检查一下包的某些状态,或者数据的某些状态,只有在满足达到一定条件下才能执行剩下的流程,否则则不执行包或者报错。这类设计在各种不同的BI项目中可能都存在,那么其原型就是利用 Execute SQL Task 中的 Single Row返回的值来控制流程。 有以下几个步骤:- SQL Statement 中的语句返回单行记录,可以多列。
- 需要在包中实现定义变量用来接收 Single Row 的某列上的值。
- 使用 Precedence Constraint 和变量来控制流程。
Single Row 案例
一条简单的查询语句返回一个单行双列的记录。 设置好连接并放好SQL语句,选择 Single Row。 添加三个变量- EXISTING_COUNT - 用来接收查询中的返回的COUNT数。
- MAX_DATE - 用来接收查询中返回的最大时间。
- TEST_DATE - 测试时间,比如说 2014-01-01。
public void Main() { // TODO: Add your code here //User::EXISTING_COUNT,User::MAX_DATE,User::TEST_DATE string existing_count = Dts.Variables["User::EXISTING_COUNT"].Value.ToString(); string max_date = Dts.Variables["User::MAX_DATE"].Value.ToString(); string test_date = Dts.Variables["User::TEST_DATE"].Value.ToString(); MessageBox.Show("Existing Count - " + existing_count + ", test date - " + test_date + ", max date - " + max_date); Dts.TaskResult = (int)ScriptResults.Success; }
保存并执行包,流程走了右边,因为 MAX DATE 要比 TEST DATE 大。
修改 TEST_DATE,那么 TEST DATE 要比 MAX DATE 大。 这就是 Execute SQL Task 中 Single Row 的使用方法。FULL Result Set
通常在 Execute SQL Task 中使用到 FULL Result Set 就一定会结合 Foreach Loop 来使用,所适用的场景是循环便利查询结果集中的每一行数据,将每一行的数据其中某几列取出来放入到变量中,然后同样在 Foreach Loop 中的其它控制流控件使用这些变量做一些操作。疑问
像这种遍历为什么不放到数据库中直接遍历不是更好吗?问题就在于,有时操作的对象并不仅仅是数据库中的表对象,而是涉及到不同的文件处理,这时就需要使用这种方式了。比如,我的目标数据源中有几百个文件,且文件的命名包括产品的名称,现在表中有产品名称的记录。需要循环遍历表中的产品,并对比哪些文件命名包含有这些产品名称,包含产品名称的文件则抽取数据,不包含产品名称的文件则移到其它目录。 下面使用这个查询作为一个示例结果集 - 使用 FULL RESULT SET 定义如下几个变量 - ORDER_SET 用来保存 Execute SQL Task 查询中的结果集,其它变量用来在循环每一行的时候保存每一列的值。注意:返回的结果集要使用 OBJECT 类型的变量来保存,这个 OBJECT 类型在内部以集合的形式存在并保存整个查询结果集。 EXECUTE SQL TASK 的 Mapping。Foreach 下的设置
我们可以通过 Foreach 的方式循环遍历这个返回的集合。添加一个 Foreach 控件,并设置遍历方式 - Foreach ADO Enumerator 和要遍历的集合对象。 遍历这个集合的时候,每次返回一行,这一行也是一个集合,通过设置索引0,1,2 将这个集合的元素赋值给各个变量。 在 Foreach 控件中添加一个 Script Task 用来显示每一行中各列的内容。 Script 中的脚本代码如下:public void Main() { // TODO: Add your code here //User::PRODUCT_ID,User::SALES_ORDER_DETAIL_ID,User::UNIT_PRICE string detailID = Dts.Variables["User::SALES_ORDER_DETAIL_ID"].Value.ToString(); string productID = Dts.Variables["User::PRODUCT_ID"].Value.ToString(); string unitPrice = Dts.Variables["User::UNIT_PRICE"].Value.ToString(); MessageBox.Show("Detail ID - " + detailID+", Product ID - "+productID +", Unite Price - "+ unitPrice); Dts.TaskResult = (int)ScriptResults.Success; }
保存并执行包,第一次循环的结果是失败的,但是可以看出来我们的结果集返回是没有问题的。
出错的原因如下: Error: The type of the value (String) being assigned to variable "User::SALES_ORDER_DETAIL_ID" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: The type of the value (String) being assigned to variable "User::PRODUCT_ID" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: The type of the value (String) being assigned to variable "User::UNIT_PRICE" differs from the current variable type (Decimal). Variables may not change type during execution. Variable types are strict, except for variables of type Object. 这是由于在集合中的数据默认都是 String 类型的,是不能够直接转换成我们定义的变量所指定的类型,因此需要修改我们的变量类型。 再次执行就可以看到遍历的效果,第一次 -最后一次 -
Script Task 中的循环遍历
同样的 Object 对象,不仅仅可以在 Foreach 中循环遍历,也可以直接在 Script Task 中循环遍历。 添加一个 Script Task,把结果集对象放入变量列表中。 脚本代码如下 -using System.Data.OleDb; public void Main() { // TODO: Add your code here OleDbDataAdapter adapter = new OleDbDataAdapter(); DataTable dataTable = new DataTable(); adapter.Fill(dataTable, Dts.Variables["User::ORDER_SET"].Value); foreach (DataRow row in dataTable.Rows) { MessageBox.Show(row[0] + "," + row[1] + "," + row[0]); } Dts.TaskResult = (int)ScriptResults.Success; }
一样可以实现遍历的效果 -
最后一条记录-总结
通过讲解 Execute SQL Task 的两种查询结果返回方式 Single Row 和 Full Result Set,实际上由此引申出了何时使用 Single Row 以及 Full Result Set 的场景。熟悉和掌握这些场景可以帮助我们在复杂的 ETL 项目中找出各种不同的解决方案,可以非常灵活的解决一些实际问题。 再次,特别通过 Foreach Loop Container 以及 Script Task 学习到了两种解析 Full Result Set 的方式,其中涉及到的知识点以及细节还是比较多的,特意总结下来希望可以帮助到大家。更多 BI 文章请参看 如果觉得这篇文章看了对您有帮助,请帮助推荐,以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。