Life Interesting Bit: 优化的 LookUp Transformaction (zt)

在介绍 Lookup Transformation 之前，我们先熟悉一下两个英文词汇：

Lookup 表: 即需要导出数据的数据源表 (或视图)
Reference 表：即在Lookup 操作中使用的数据表(或查询)

Lookup Transformation 是 SSIS 中应用最为广泛的 Transformation 之一，它用于实现类似于 SQL 语句中个 INNER JOIN 功能，在建设数据仓库过程中，更是产生 Surrogate Key 必不可少的组件。根据 SSIS 2005 实际应用中开发人员的反应，Microsoft 在 SSIS 2008 中对 Lookup Transformation 的性能做了很重要的改动，增加了一个共享文件类型的Cache，文件Cache 是将 Reference表的记录存放在一个文件中可以供所有使用同一Reference表的Lookup 使用，并且基本上不消耗额外的内存，所以这种方式能处理很大的Reference 表，更加适应企业级的ETL 开发。SSIS 2008 支持三种类型的 Cache:

Full Cache, 默认情况下，Lookup 使用Full Cache 模式。在这种模式下，所以的Reference 表的记录将被一次性地导入到内存中，在以后的处理中将 SSIS 将在内存中调用 Reference 表的内容。这种方式将会使用大量的内存，并且不需要 Reference 表提前建立索引，整个的传输处理将在所有的Reference记录导入到内存后开始。当 Reference Table 记录数很大的时候，这种方式最快，但需要大容量内存的支持。
Partical Cache，在最初始状态下，Cache 是空的。数据流操作从Lookup 表数据源读取一条新纪录到内存后，首先查找Cache中符合条件的 Reference 表的记录，如果没有找到，则从Reference 表的数据源中查找，并将查到的记录加如到Cache 中。这种方式不等待 Reference 表中的所有记录导入到Cache中再开始数据流操作，而是立刻进行数据从数据源到目标的传输。当 Lookup 表中的对应Reference 表的外键不是很大的时候，这种方式很快。
No Cache，不使用Cache。这是速度最慢的一种 Lookup, 当系统内存不足以建立Cache的时候使用。但是由于 SSIS 2008 怎加了文件Cache的功能，所以这种方式的Cache用途不大。

下面的图就是SSIS 2008 中Lookup Transformation 的属性编辑界面。

在图中，只有当选择 Full cache 模式的时候，才可以使用 Cache connection manager, 这种情况下将使用一个预先建立好的Cache文件作为Reference 表，而不是从Reference的数据源读取数据，这种方式将直接使用Cache 文件，而不需要再将Cache 文件读到内存中。于此不同，如果使用 OLE DB connection, 将会建立和使用 OLE-DB 连接到 Reference 表，所有的记录读入内存中形成Cache。

使用 Full Cache 模式下的文件Cache 有下面的几个好处：

节约内存，因为使用文件Cache, 并不会在内存中形成Cache。Cache 文件是一个索引了的特殊的文件格式，所以读取速度也很快。
共享文件Cache, 如果很多数据流操作使用同一个Reference 表，可以共享这个文件Cache, 而不需要为每一个数据流单独建立相同的Reference 表。

下面通过一个例子，我来介绍使用SSIS 2008 的这个新的功能。

从AdventureWorks 选择两个表：HumanResources.Employee 和 Person.Contact, 这两个表的关联如下图所示：

在图中，ContactID 做为外键连接了Employee,VendorContact 和 Contact 表，我们可以用一个简单的SQL 查看它们的关联：

SELECT con.FirstName,con.LastName,emp.EmployeeID,emp.BirthDate,emp.gender
FROM HumanResources.Employee emp
INNER JOIN Person.Contact con ON emp.ContactID = con.ContactID
假设我们要把记录导出到SSISTraining Database下的一个新表：NewEmployee 表，这个新的表包含了Employee 和 Contact 两个表的内容：
USE [SSISTraining]
GO
CREATE TABLE [dbo].[NewEmployee]
(
[EmployeeID] [int] PRIMARY KEY NOT NULL,
[FirstName] [nvarchar] (50) NULL,
[LastName] [nvarchar] (50) NULL,
[BirthDate] [datetime] NULL,
[gender] [nchar] (1) NULL
)
GO
下面我们就来用这个例子来演示 SSIS 2008 的这个新的功能

新建一个SSIS 项目，把默认的SSIS Package 修改为CreateNewEmployee
使用OLEDB 建立到两个数据库的连接，分别命名为：AdventureWorks 和 SSISTraining
将第一个 Data Flow Task 添加到Control Flow 页面中，并将其命名为 Create Cache File
双击这个数据流任务，将一个 OLEDB Source 添加到 Data Flow 页面中，设置其OLE DB connection manager 为 AdventureWorks，并使用下面的SQL 语句获得数据：
从工具箱中的 Data Flow Transformations 选择 Cache Transformation 并将其添加到 Data Flow 页面中，双击这个 Cache Tranformation进行编辑。
首先需要建立一个到 cache 文件的连接，这个特殊的连接用来连接到特殊的Cache 文件，文件的扩展名必须为 caw.

在图中，选中 Use file cache，并将文件名设置为 C:\MyCache.caw，然后再单击 Columns 页面选择适当的关键值列。在我们的例子中，pipeline 中有三个列：ContactID，FirstName 和LastName, 其中FirstName 和 LastName 是需要返回值的列，而 ContactID 是需要和Lookup 表进行Join 的列，这个列通常叫做 Key，这个Key 列(或列的组合) 需要在图中Index Position 中赋予一个序号，表示我们需要按Key 列进行排序，第一个列从1 开始，如果有三个Key 列，那么他们的Index Position将分别是1，2，3.
保存以上的设置后，我们还需要在图中设定 Cache 文件和源数据的映射关系：
保存以上的设置。完成了的 Data Flow 页面如下图所示：
上面的操作将在运行中将Reference 表的内容存放在Cache 文件中，下面的步骤用来访问这个 Cache 文件。在 Control Flow 页面中再加入第二个Data Flow Task，将这个新的Control Flow 命名为 Load Employee Table，双击打开它的 Data Flow 页面；
首先使用一个 OLE DB　数据源连接到 AdventureWorks 下的 HumanResources.Employee 表，使用下面的SQL 语句从表中取得记录：
然后再将一个 Lookup Transformation 添加到 Data Flow 页面中，连接 OLEDB 数据源到到这个 Lookup Transformation，双击 Lookup 打开 Lookup Tranformation 设置页面。在General 页面中选择 Full Cache 下的 Cache Connection Manager 连接类型；在 Connection 页面下选择刚刚建立的 Cache 文件连接；在 Columns 页面中选择 ContactID 作为Lookup 列，Firstname 和 Lastname 作为返回值的列。
保存并运行这个SSIS Package。