开篇介绍
Execute SQL Task 这个控件在微软BI ETL 项目中使用的频率还是非常高的,也是大部分入门 SSIS 初学者最早接触到的几个控制流控件。
我们通常使用 Execute SQL Task 的场景包含但不止于以下几类:
- 在从源端加载数据到 Staging 表之前使用 Execute SQL Task 执行一些 Truncate 操作。
- 执行一些 Log 的插入,更新操作。
- ETL 过程中的 Merge 语句操作。
- XML 的输出处理。
关于如何使用 Execute SQL Task 就不在本文讲解了,包括参数传递 Input,存储过程返回值 Output,包括 Return Value ,还有 SQL Source Type 等不同方式以及 OLD DB,ADO.NET 区别等基础概念文本也不会涉及到。
本文要讲解的内容是针对 ResultSet 的几种类型:None, Single row, Full result set, XML。其中 None 不用讲解,关于 XML 的内容大家可以参考我的另一篇文章 两种将 SQL Server 数据库数据输出成 XML 文件的方法,因此在这里只讲解 Single Row 和 Full Result Set 的处理方法。
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。
0 表示 Single Row 中的第一列,1 表示 Single Row 中的第二列。
下面放两个空的 Data Flow Task 并关联到 Execute SQL Task,点击连接线设置条件控制。
DFT_TEST_AFTER_MAX 上的连接线条件判断为:DATEDIFF( "dd", (DT_DBDATE) (@[User::TEST_DATE]) , (DT_DBDATE) (@[User::MAX_DATE])) >=0
DFT_TEST_BEFORE_MAX上的判断表达式为:DATEDIFF( "dd", (DT_DBDATE) (@[User::TEST_DATE]) , (DT_DBDATE) (@[User::MAX_DATE])) <0
左右两个数据流下各加入一个 Script Task 显示一下这几个变量的值,代码如下:
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 的方式,其中涉及到的知识点以及细节还是比较多的,特意总结下来希望可以帮助到大家。