一步一步学习BI（3）Integration Services创建ETL包

Microsoft Integration Services 是一个可以生成高性能数据集成解决方案（包括为数据仓库提取、转换和加载 (ETL) 包）的平台。

Integration Services 包括用于生成和调式包的图形工具和向导；用于执行工作流函数（如 FTP 操作）、执行 SQL 语句以及发送电子邮件的任务；用于提取和加载数据的数据源和目标；用于清除、聚合、合并和复制数据的转换；用于管理包执行和存储的管理服务，即 Integration Services；以及用于 Integration Services 对象模型编程的应用程序编程接口 (API)。

说明：

本文是作者在学习Integration Services过程中的笔记，基本思路和内容都来自SQL Server 联机丛书。觉得园子里的BI方面的资源比较少，就放上来了。

学习本文需要建立在对Integration Services基本了解的基础。

如果没有任何了解，请参考一步一步学习BI（1）-认识Integration Services 

本文中将用到的数据库为微软的AdventureWorksDW 

本文中将用到的文本数据在这里 

请先下载这两份数据文件，我们在接来实验中将要用到。 

目标：

在 本文中我们将学习如何使用 SSIS 设计器创建一个简单的 Microsoft SQL Server Integration Services 包。所创建的包将从平面文件提取数据，重新设置数据的格式，然后将已重新设置格式的数据插入到事实数据表中并将扩展包以阐释循环、包配置、日志记录和错误 流。

步骤：

1. 创建项目和基本包

   • 这一小节中我们将创建一个简单的ETL包，该包可以从单个平面文件源（TXT文件）提取数据，使用两个查找转换组建转换该数据，然后将该数据写入AdventureWorksDW中的FactCurrencyRate事实数据表中。

     1. 新建一个Integration Services Project, 在新建的时候Project的时候取名为SSISTutorial。将默认的包文件Package.dtsx重命名为Lesson1.dtsx。

     2. 右键单击工作区的下方的Conection Managers，在右键菜单中选择“New Flat File Connection”

     3. 在弹出Flat File Connection Manager Editor中Connection manager name取名为Sample Flat File Source Data。单击Brower,在打开的对话框中选择SampleCurrencyData.txt文件。

     4. 在 Flat File Connection Manager Editor中选择Advanced(高级)选项，配置每一行的属性。

        • 将 Column 0 名称属性改为 AverageRate

        • 将 Column 1 名称属性改为 CurrencyID

        • 将 Column 2 名称属性改为 CurrencyDate

        • 将 Column 3 名称属性改为 EndOfDayRate

        • 

        • 点 击Suggest Types（建议类型）。Integration Services 将根据前 100 行数据自动建议最合适的数据类型。在弹出的窗口中不需要做任何更改，点击OK(确定)将获取由Integration Services建议的数据类型，如果点击Cancle(取消)，则不对列元数据提供任何建议，并使用默认字符串 (DT_STR) 数据类型。

        • 但是因为 CurrencyID 和 CurrencyDate 列建议的数据类型与目标表中的字段的数据类型不兼容。由于 DimCurrency.CurrencyAlternateKey 的数据类型为 nchar (3)，CurrencyID 必须从字符串类型 [DT_STR] 改为字符串类型 [DT_WSTR]。另外，字段 DimTime.FullDateAlternateKey 被定义为 DataTime 数据类型，因此 CurrencyDate 需要从日期类型 [DT_Date] 改为数据库时间戳类型 [DT_DBTIMESTAMP]。所以我们需要在“属性”窗格中，将列 CurrencyID 的数据类型从字符串类型 [DT_STR] 改为 Unicode 字符串类型 [DT_WSTR]，将列 CurrencyDate 的数据类型从日期类型 [DT_DATE] 改为数据库时间戳类型 [DT_DBTIMESTAMP]，最后确定。

     5. 右 键单击工作区的下方的Conection Managers，在右键菜单中选择“New OLE DB Connection”, 在弹出的Configure OLE DB Connection Manager弹出窗口中选择新建，在Connection Manager窗口中，Sever name本地，验证方式为Windows验证，数据库选择AdventureWorksDW，最后确定。  

     6. 在主工作窗口上方点击“控制流”选项卡，并从左边的工具箱中拖拽一个“数据流任务”到控制流设计面板上，命名为：Extract Sample Currency Data。注：好的做法是为添加到设计图面的所有组件提供唯一的名称。考虑到易用性和可维护性，名称应说明每个组件执行的功能。这个与编程开发中是一致的。

     7. 双击刚刚建立的数据流任务“Extract Sample Currency Data” 进入到数据流设计面板，从左边的工具箱中拖拽一个“Flat File Source” 到设计面板中并重命名为“Extract Sample Currency Data”， 双击“Flat File Source” 在弹出的编辑窗口中为Flat File connection manager选择我们之前建立的“Sample Flat File Source Data”, 确定。

     8. 配置了用于从源文件提取数据的平面文件源后，下一个任务是定义获取 CurrencyKey 和 TimeKey 的值所需的查找转换。查找转换通过将指定输入列中的数据连接到引用数据集中的列来执行查找。引用数据集可以是现有的表或视图，也可以是新表或 SQL 语句的结果。在这里，查找转换使用 OLE DB 连接管理器连接到包含引用数据集的源数据的数据库。我们将向包中添加以下两个查找转换组件并对其进行配置：

        • 一个转换是根据平面文件中匹配的 CurrencyID 列值对 DimCurrency 维度表的 CurrencyKey 列中的值执行查找。
        • 一个转换是根据平面文件中匹配的 CurrencyDate 列值对 DimTime 维度表的 TimeKey 列中的值执行查找。
     9. 从工具向中拖拽一个Lookup(查找)组建到数据流设计窗体中命名为Lookup Currency Key，将Extract Sample Currency Data 的输出作为Lookup Currency Key的输入将两个组件链接起来。双击Lookup Currency Key，在General(常规)中Cache Mode默认为Full cache, Connection type默认为OLE DB connection manager.在Connection选项中，OLE DB connection manager选择之前建立好的“LocalHost.AdventureWorksDW”, 选择Use results of an SQL query:

        01	select * from (select * from [dbo].[DimCurrency]) as refTable

        02	where [refTable].[CurrencyAlternateKey] = 'ARS'

        03

        OR

        04	[refTable].[CurrencyAlternateKey] = 'AUD'

        05

        OR

        06	[refTable].[CurrencyAlternateKey] = 'BRL'

        07

        OR

        08	[refTable].[CurrencyAlternateKey] = 'CAD'

        09

        OR

        10	[refTable].[CurrencyAlternateKey] = 'CNY'

        11

        OR

        12	[refTable].[CurrencyAlternateKey] = 'DEM'

        13

        OR

        14	[refTable].[CurrencyAlternateKey] = 'EUR'

        15

        OR

        16	[refTable].[CurrencyAlternateKey] = 'FRF'

        17

        OR

        18	[refTable].[CurrencyAlternateKey] = 'GBP'

        19

        OR

        20	[refTable].[CurrencyAlternateKey] = 'JPY'

        21

        OR

        22	[refTable].[CurrencyAlternateKey] = 'MXN'

        23

        OR

        24	[refTable].[CurrencyAlternateKey] = 'SAR'

        25

        OR

        26	[refTable].[CurrencyAlternateKey] = 'USD'

        27

        OR

        28	[refTable].[CurrencyAlternateKey] = 'VEB'

     10. 在Columns(列)选项卡中，将Available Input Columns面板中的CurrencyID拖拽到Available Lookup Columns中的CurrencyAlternateKey上，并将CurrencyKey的复选框选上。
     11. 
     12.   从工具向中拖拽一个Lookup(查找)组建到数据流设计窗体中命名为Lookup DateKey。编辑Lookup DateKey,在连接选项上的“OLE DB 连接管理器”对话框中，确保显示 localhost.AdventureWorksDW。 在“使用表和视图”框中，选择[dbo].[DimTime]。在Columns(列)选项卡中，将Available Input Columns面板中的CurrencyDate拖拽到Available Lookup Columns中的FullDateAlternateKey上，并将TimeKey的复选框选上。将Lookup Currency Key的输出作为Lookup DateKey的输入，在弹出框中的选择类型中选择Lookup Match Output.
     13. 在ToolBox中，展开Data Flow Destinations，将OLE DB Destination拖拽到Lookup DateKey下面，命名为Sample OLE DB Destination. 将Lookup DateKey的 输出作为Sample OLE DB Destination的输入，将组件连接起来，在在弹出框中的选择类型中选择Lookup Match Output.双击Sample OLE DB Destination，确定OLE DB connection manager的连接为LocalHost.AdventureWorkDW. 在Name of the table or the View框中选择[dbo].[FactCurrencyRate]。单击左边的Mappings,验证 AverageRate、CurrencyKey、EndOfDayRate 以及 TimeKey 输入列是否已正确映射到目标列。如果映射了同名列，则说明映射正确。
     14.   
     15. 执行Lesson1包，正确执行，我们发现共有1097条数据按照我们的要求导入到了目标数据库中

2. 添加循环

   • 在上一节中我们创建了一个基本包，创建了从单个平面文件源提取数据的包，然后使用查找转换功能对数据进行了转换，最后将数据加载到 AdventureWorksDW示例数据库的FactCurrencyRate事实数据表中。但是提取，转换和加载（ETL）过程很少使用单个平面文 件。典型的ETL过程从多个平面文件源提取数据。从多个源提取数据需要采用迭代控制流。Microsoft Integration Services最可能出现的功能之一就是方便快捷地向包中添加迭代或循环。Integration Services为循环遍历包提供了两种容器类型：Foreach循环容器和For循环容器。Foreach循环容器使用枚举器执行循环，而For循环通 常使用变量表达式。

   • Foreach 循环容器使包能够对指定枚举器的每个成员重复执行控制流。使用Foreach 循环容器，可以枚举：

     • ADO记录集行和架构信息

     • 文件和目录结构

     • 系统，包和用户变量

     • SQL Server管理对象（SMO）

   • 1. 打开上一小节的工程文件，选中Lesson1.dtsx 包文件，复制这个包文件，选择SSISTutorial后粘贴。将复制过来的Lesson1.dtsx 重命名为Lesson2.dtsx。这一小节中我们将在Lesson2.dtsx 这个包文件中进行开发。

     2. 从ToolBox中拖拽组件Foreach Loop Container 到控制流面板上，命名为Foreach File in Folder后 双击打开。在Foreach Loop Editor的Collection中选择Foreach File Enumerator。在Enumerator configuration中选择一个文件夹，这里我们选择准备好的Sample Data文件夹，Files填写为Currency_*.txt。

     3. 

     4. 从左边选择Variable Mappings(变量映射)，在Variable列中选择 New Variable 。在弹出的Add Variable对话框中Name 输入 varFileName。确定Add Variable 对话框，确定Foreach Loop Editor.

     5. 

     6. 将 Extract Sample Currency Data 数据流任务拖动到现已重命名为 Foreach File in Folder 的 Foreach 循环容器中,如下图所示。

     7. 
     8. 在Connection Managers 处选择先前建立好的Sample Flat File Source Data 平面文件连接，右键单击 Sample Flat File Source Data，再选择Properties.在Properties窗口中的Expressions窗格中，点击省略号，打开Property Expressions Editor窗口，在Property 列的下拉菜单中选择ConnectionString.
     9. 
     10. 点击Expressions列的省略号按钮，打开Expression Builder窗口，在窗口的左上将Varibales展开，将VarFileName::Variable拖拽到Expression窗口中。确定打开的窗口回到控制流工作面板。
     11. 
     12. 又是一次激动人心的时刻到了，执行Lesson2.dtsx，按照我们的设想路径下的所有文件都按规则被导入到目标数据库中。赶快检查数据库去吧，或者从输出窗口中也可以看到循环输出成功的信息。
     13. 

3. 添加包配置

   • 包配置允许从开发环境的外部设置运行时属性和变量。有了配置我们就能开发灵活且易于部署和分发的包。Microsoft Integration Services提供了以下配置类型：

     • XML配置文件

     • 环境变量

     • 注册表项

     • 父包变量

     • SQL Server表

   • 使用包配置向导，将创建一个XML配置，以便通过使用映射到Directory属性的包级别变量来更新Foreach循环容器的Directory 属性。在创建配置文件后，将从开发环境的外部修改该变量的值，并将修改后的属性指向新的示例数据文件夹。再次运行包时，配置文件将填充该变量的值，而该变 量又会更新Directory属性。结果，包将迭代遍历新数据文件夹中的文件，而不是迭代遍历在该包中硬编码的原始文件夹中的文件。

     1. 将Lesson2.dtsx 复制，粘贴，重命名为Lesson3.dtsx，这一小节将在Lesson3.dtsx这个包上做开发。

     2. 首先创建映射到Directory属性的新的包级别的变量。既然是包级别的就是要先选择我么要操作的包。在Lesson3.dtsx包的“Control Flow” 面板的选项卡上单击，这会将要创建的变量的作用域设置为包。

     3. 在 SSIS 菜单中，选择Variables调出边栏，新增一个变量varFolderName，数据类型为String。

     4. 

     5. 回到Control Flow工作面板，双击Foreach File in Folder容器，在Foreach Loop Editor的Collection中的Expressions添加了我们刚改添加的包级别的变量。确定后回到控制流面板。

     6. 

     7. 在SSIS菜单上选择Package Configurations…,在弹出的窗口中首先Enable package configurations,然后增加一个包配置。在包配置向导中Next，在Configuration file name中Browse到工程目录，输入SSISTutorial作为文件名。Next, 在Slect Properties to Export的Objects中展开Variables,展开varFolderName,展开Properties,选择Value。Next, 在Completing the Wizard中Configuration name 为SSIS Tutorial Directory configuration，完成。

     8. 到刚才保存的工程目录下找到SSISTutorial.dtsConfig文件，这个一个xml文件，用记事本打开，找到 Configuration节点下的ConfiguredValue配置项，配置一个路径。这样比较Lesson2的包文件，当我们要导入不同文件夹下的 文本文件时只需要修改这个配置文件就可以了。而不是像Lesson2中那样Hard Code在代码中。

     9. 执行Lesson3.dtsx包，将会将所配置路径下的文本文件循环按照规则导入到目标数据库中。

4. 添加日志记录

   • Microsoft Integration Services包含日志记录功能，这些功能可以使我们可以通过提供任务和容器事件跟踪来对包执行疑难排解和监控。日志记录功能非常灵活，可以在包级别或 者在包中的各个任务和容器上启用。可以选择要记录的事件，也可以对单个包创建多个日志。

   • 日志记录由日志提供程序提供。每个日志提供程序可以将日志记录信息写入不同的格式和目标类型。Integration Services提供了以下日志提供程序：

     • 文本文件

     • SQL Server Profiler

     • Windows事件日志

     • SQL Server

     • XML 文件

   • 在本节中我们将在上节的基础上添加并配置日志记录，以在包执行过程中监控特定事件。

     1. 将Lesson3.dtsx 复制，粘贴，重命名为Lesson4.dtsx，这一小节将在Lesson4.dtsx这个包上做开发。

     2. 在SSIS菜单上选择Logging….,在弹出的配置窗口中Provider type选择SSIS log provider for Text files. 然后单击Add.【Integration Services 将向包中添加一个默认名称为用于文本文件的 SSIS 日志提供程序的新文本文件日志提供程序。现在便可对新的日志提供程序进行配置。】，在名称中填写Lesson 4 Log File，在配置列中选择新建，Usage Type选择Create file，File点击Browse将打开工程目录，输入TutorialLog.log作文日志文件名。

     3. 

     4. 在Containers窗格中，展开包容器层次结构中的所有节点，然后清除包括 Extract Sample Currency Data 复选框在内的所有复选框。现在选中 Extract Sample Currency Data 复选框以仅获取有关此节点的事件。在Details选项卡的Events列中，选择 PipelineExecutionPlan 和 PipelineExecutionTrees 事件。

     5. 

     6. 在Providers选项卡上的Name列中，选择 Lesson 4 Log File。为包创建日志提供程序后，可以选择取消选择它以临时关闭日志记录，而不必删除后再重新创建日志提供程序。

     7. 执行Lesson4.dtsx包，到工程目录下去查看我们的日志文件吧。

5. 添加错误留重定向

• 为了处理在转换过程中可能发生的错误，Microsoft Integration Services 使我们能够基于每个组件和每个列来决定如何处理无法转换的数据。可以选择忽略某些列中的失败，重定向整个失败的行或者只是使组建失败。默认情况 下，Integration Services中的所有组件被配置为在发生错误时失败。而使组件失败又会导致包失败，并使所有后续处理停止。

• 如果不让失败导致包停止执行，一个好的方法是通过配置使在转换中发生潜在处理错误是这些错误能够得到处理。虽然可能选择忽略失败以确保包陈宫运行， 但通常更好的做法是将失败的行重定向到另一个处理路径，在这里可以使数据和错误持久化，接受检查并在随后的某个时间对其进行重新处理。

• 在本节的开发包中，将创建一个实例数据文件的损坏版本，损坏的文件将在运行包时强制发生处理错误。为了处理错误数据，我们将添加并配置一个平面文件目标，它会将所有无法在Lookup Currency Key转换中找到查找值的行写入文件。

• 将错误数据写入文件之前，需要包括一个使用脚本获取错误说明的脚本组件。然后，将重新配置Lookup Currency Key转换，以便将所有无法处理的数据重定向到脚本转换中。

1. 将Lesson4.dtsx 复制，粘贴，重命名为Lesson5.dtsx，这一小节将在Lesson5.dtsx这个包上做开发。

2. 创建损坏的文本文件包。在本工程的下载文件中有个Sample Data2文件夹，我们将打开其中的 Currency_VEB.txt 文件，并使用文本编辑器的查找和替换功能，查找 VEB 的所有实例，并替换为 BAD。在包含其他示例数据文件的同一文件夹中，将修改后的文件另存为 Currency_BAD.txt保存在Sample Data2文件夹中。

3. 删除工程目录下的日志文件，执行一下Lesson5.dtsx，看看是否有错误信息。Error: 0xC020901E at Extract Sample Currency Data, Lookup Currency Key [26]: Row yielded no match during lookup.

4. 在Data Flow面板上从Data Flow Transformations中拖拽一个Script Component组件到面板上，Lookup Currency Key的右下方，在弹出的Type选择上选择Transgormation。将Lookup Currency Key的红色箭头拖拽至这个组件上，在弹出的Configure Error Output中将Error列的类型选择为Redirect row。

5. 

6. 重命名组件为Get Error Description，双击Get Error Description，在Script Transformation Editor中的Input Columns选项，Input Column 选择ErrorCode列。

7. 

8. 在Inputs and Outputs选项上，展开Output 0, 展开Output Columns，点击Add Column，取名为ErrorDescription，并设置数据类型为string [DT_WSTR]。

9.  

10. 选择Script 选项，单击Edit Script…, 打开ssisscript，重写其中的方法后确定。

    1	public override void Input0_ProcessInputRow(Input0Buffer Row)

    2

    {

    3	Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

    4

    5

    }

11. 回到Data Flow工作面板，为Get Error Description添加一个平面目标文件作为错误日志的输出记录。从Toolbox中拖拽一个Flat File Destination到Get Error Description下方并命名为Failed Rows。双击打开Flat File Destination Editor，New…一个Flat File connection manager,在弹出窗口的类型选择中选择Delimited。Conection manager name 中命名为Error Data，Browser 将打开工程所在的文件路径，取文件名为ErrorOutput.txt。回到Flat File Destination Editor中将Overwrite data in the file前面的勾取消。点击Mappings，确认所有的列都是正确的对应上了。

12. 执行包Lesson5,包将被正确执行，错误数据将记录到ErrorOutput.txt文件中，赶快看看去吧。

终于是写完了，有点长和有点罗嗦。

希望本文对你有所帮助。

本文完整工程文件下载为：SSISTutorial.zip