SQL Server Integration Services教程 1： 创建简单ETL包

在本教程中，您将创建一个简单 ETL 包，该包可以从单个平面文件源提取数据，使用两个查找转换组件转换该数据，然后将该数据写入 AdventureWorksDW 中的 FactCurrencyRate 事实数据表。在本教程中，您还将学习如何创建新包、添加和配置数据源和目标连接以及使用新的控制流和数据流组件。

了解包要求

在创建包之前，需要充分了解在源数据和目标数据中使用的格式。了解了这两种数据格式后，才能定义将源数据映射到目标数据所需的转换。
查看源
对于本教程，源数据是一组包含在平面文件 SampleCurrencyData.txt 中的历史货币数据。源数据有以下四列：货币的平均汇率、货币键、日期键和当天汇率。
下面是 SampleCurrencyData.txt 文件中所包含的源数据示例：
1.00010001   ARS   9/3/2001 0:00    0.99960016
1.00010001   ARS   9/4/2001 0:00    1.001001001
1.00020004   ARS   9/5/2001 0:00    0.99990001
1.00020004   ARS   9/6/2001 0:00    1.00040016
1.00050025   ARS   9/7/2001 0:00    0.99990001
1.00050025   ARS   9/8/2001 0:00    1.001001001
1.00050025   ARS   9/9/2001 0:00    1
1.00010001   ARS   9/10/2001 0:00   1.00040016
1.00020004   ARS   9/11/2001 0:00   0.99990001
1.00020004   ARS   9/12/2001 0:00   1.001101211

在使用平面文件源数据时，需要了解平面文件连接管理器如何解释平面文件数据，这一点很重要。如果平面文件源是 Unicode 编码的，则平面文件连接管理将所有列定义为 [DT_WSTR]，默认列宽为 50。如果平面文件源是 ANSI 编码的，则将列定义为 [DT_STR]，默认列宽为 50 。您可能必须更改这些默认设置，才能使字符串列类型与所使用的数据更相符。为此，您需要查看将写入数据的目标的数据类型，然后在平面文件连接管理器中选择正确的类型。
查看目标
源数据的最终目标是 AdventureWorksDW 中的 FactCurrencyRate 事实数据表。FactCurrencyRate 事实数据表有四列，并且与两个维度表有关系，如下表所示。

列名         数据类型         查找表         查找列
AverageRate        float        无        无
CurrencyKey        int (FK)        DimCurrency        CurrencyKey (PK)
TimeKey        Int (FK)        DimTime        TimeKey (PK)
EndOfDayRate        float        无        无
将源数据映射为与目标兼容
对源数据和目标数据的分析指出需要查找 CurrencyKey 和 TimeKey 值。将执行这些查找的转换通过使用 DimCurrency 和 DimTime 维度表中的备用键来获取 CurrencyKey 和 TimeKey 值。

平面文件列         表名         列名         数据类型
0        FactCurrencyRate        AverageRate        Float
1        DimCurrency        CurrencyAlternateKey        nchar(3)
2        DimTime        FullDateAlternateKey        Datetime
3        FactCurrencyRate        EndOfDayRate        Float

创建新的 Integration Services 项目   
在 Microsoft SQL Server 2005 Integration Services (SSIS) 中创建包的第一步就是创建一个 Integration Services 项目。此项目包含在数据转换解决方案中使用的数据源、数据源视图和包等对象的模板。
创建新的 Integration Services 项目
1.        在“开始”菜单上，依次指向“所有程序”、“Microsoft SQL Server 2005”，再单击 SQL Server Business Intelligence Development Studio。
2.        在“文件”菜单上，指向“新建”，再单击“项目”，以创建一个新的 Integration Services 项目。
3.        在“新建项目”对话框的“模板”窗格中，选择“Integration Services 项目”。
4.        在“名称”框中，将默认名称更改为 SSIS Tutorial。
5.        单击“确定”。
默认情况下，将创建一个名为 Package.dtsx 的空包，并将该包添加到项目中。
6.        在解决方案资源管理器工具栏上，右键单击 Package.dtsx，再单击“重命名”，将默认包重命名为 Lesson 1.dtsx。
7.        （可选）在系统提示重命名包对象时，单击“是”。
添加和配置平面文件连接管理器   
在本任务中，将在刚创建的包中添加一个平面文件连接管理器。通过平面文件连接管理器，包可从平面文件中提取数据。使用平面文件连接管理器，可以指定包从平面文件中提取数据时要应用的文件的名称与位置、区域设置与代码页以及文件格式，其中包括列分隔符。另外，还可以为各个列手动指定数据类型；也可以使用“提供列类型建议”对话框，自动将提取出来的数据列映射到 Integration Services 数据类型。
必须为要使用的每种文件格式创建一个新的平面文件连接管理器。因为本教程从多个数据类型完全相同的平面文件提取数据，所以只需在包中添加和配置一个平面文件连接管理器。
在本教程中，将在平面文件连接管理器中配置以下属性：
•        Column names 因为平面文件没有列名，因此平面文件连接管理器将创建默认的列名。这些默认名称不能用于标识每个列代表的内容。若要使这些默认名称更有用，需要将默认名称更改为要加载平面文件数据的事实数据表匹配的名称。
•        Data mappings 为平面文件连接管理器指定的数据类型映射，将由所有引用该连接管理器的平面文件数据源组件使用。可以使用平面文件连接管理器，或者使用“提供列类型建议”对话框来手动映射数据类型。在本教程中，将查看“提供列类型建议”对话框中建议的映射，然后在“平面文件连接管理器编辑器”对话框中手动设置必要的映射。
添加一个平面文件连接管理器
1.        右键单击“连接管理器”区域中的任意位置，再单击“新建平面文件连接”。
2.        在“平面文件连接管理器编辑器”对话框的“连接管理器名称”字段中，键入 Sample Flat File Source Data。
3.        单击“浏览”。
4.        在“打开”对话框中，浏览并找到示例数据文件夹，再打开 SampleCurrencyData.txt 文件。默认情况下，教程示例数据安装在 c:/Program Files/Microsoft SQL Server/90/Samples/Integration Services/Tutorial/Creating a Simple ETL Package/Sample Data 文件夹中。
重命名平面文件连接管理器中的列
1.        在“平面文件连接管理器编辑器”对话框中，单击“高级”。
2.        在“属性”窗格中，进行如下更改：
o        将 Column 0 名称属性更改为 AverageRate。
o        将 Column 1 名称属性更改为 CurrencyID。
o        将 Column 2 名称属性更改为 CurrencyDate。
o        将 Column 3 名称属性更改为 EndOfDayRate。
注意：

默认情况下，所有四个列最初都设置为字符串数据类型 [DT_STR]，其 OutputColumnWidth 为 50。
重新映射列数据类型
1.        在“平面文件连接管理器编辑器”对话框中，单击“建议类型”。
Integration Services 将根据前 100 行数据自动建议数据类型，但您可以更改建议选项来增加或减少取样数据，指定整数或布尔型数据的默认数据类型，也可以添加作为填充量添加到字符串列中的空格。
但现在，请不要进行任何更改，单击“取消”返回“平面文件连接管理器编辑器”对话框的“高级”窗格，查看建议的列数据类型。
在本教程中，Integration Services 为 SampleCurrencyData.txt 文件中的数据建议了下表第二列中显示的数据类型。但是，目标中的列要求的数据类型（将在以后的步骤中定义）显示在下表的最后一列。

平面文件列         建议的类型         目标列         目标类型
AverageRate        Float [DT_R4]        FactCurrencyRate.AverageRate        Float
CurrencyID        String [DT_STR]        DimCurrency,CurrencyAlternateKey        nchar(3)
CurrencyDate        Date [DT_DATE]        DimTime.FullDateAlternateKey        datetime
EndOfDayRate        Float [DT_R4]        FactCurrencyRate.EndOfDayRate        Float
为 CurrencyID 和 CurrencyDate 列建议的数据类型与目标表中的字段的数据类型不相容。由于 DimCurrency.CurrencyAlternateKey 的数据类型为 nchar (3)，CurrencyID 需要从字符串类型 [DT_STR] 更改为字符串类型 [DT_WSTR]。另外，字段 DimTime.FullDateAlternateKey 被定义为 DataTime 数据类型，因此 CurrencyDate 需要从日期类型 [DT_Date] 更改为数据库时间戳类型 [DT_DBTIMESTAMP]。
2.        在“属性”窗格中，将列 CurrencyID 的数据类型从字符串类型 [DT_STR] 更改为 Unicode 字符串类型 [DT_WSTR]。
3.        在“属性”窗格中，将列 CurrencyDate 的数据类型从日期类型 [DT_DATE] 更改为数据库时间戳类型 [DT_DBTIMESTAMP]。
4.        单击“确定”。
添加和配置 OLE DB 连接管理器   
添加了用于连接到数据源的平面文件连接管理器以后，下一个任务是添加用于连接到目标的 OLE DB 连接管理器。通过 OLE DB 连接管理器，包可以在任何 OLE DB 兼容的数据源中提取数据或加载数据。使用 OLE DB 连接管理器，可以为连接指定服务器、身份验证方法和默认数据库。
在本教程中，将创建使用 Windows 身份验证的 OLE DB 连接管理器，以连接到 AdventureWorksDB 的本地实例。本教程以后要创建的其他组件（如查找转换和 OLE DB 目标）也将引用此处创建的 OLE DB 连接管理器。
添加和配置 OLE DB 连接管理器
1.        右键单击连接管理器区域中的任意位置，再单击“新建 OLE DB 连接”。
2.        在“配置 OLE DB 连接管理器”对话框中，单击“新建”。
3.        在“服务器名称”中，输入 localhost。
将 localhost 指定为服务器名称时，连接管理器将连接到本地计算机上 Microsoft SQL Server 2005 的默认实例。若要使用 SQL Server 2005 的远程实例，请将 localhost 替换为要连接到的服务器的名称。
4.        在“登录到服务器”组中，确认选择了“使用 Windows 身份验证”。
5.        在“连接到数据库”组的“选择或输入数据库名称”框中，键入或选择 AdventureWorksDW。
6.        单击“测试连接”，验证指定的连接设置是否有效。
7.        单击“确定”。
8.        单击“确定”。
9.        在“配置 OLE DB 连接管理器”对话框的“数据连接”窗格中，确认选择了 localhost.AdventureWorksDW。
10.        单击“确定”。
在包中添加数据流任务   
为源数据和目标数据创建了连接管理器后，下一个任务是在包中添加一个数据流任务。数据流任务将封装在源和目标之间移动数据的数据流引擎，并提供在移动数据时转换、清除和修改数据的功能。大部分的数据提取、转换和加载 (ETL) 进程均在数据流任务中完成。
注意：

Microsoft SQL Server 2005 Integration Services 将数据流从控制流中分隔开来。将数据流从控制流进行分隔是 Integration Services 与 Microsoft SQL Server 2000 Data Transformation Services 的重要区别之一。
添加一个数据流任务
1.        单击“控制流”选项卡。
2.        在“工具箱”中，展开“控制流项”，并将一个数据流任务拖到“控制流”选项卡的设计图面上。
3.        在“控制流”设计图面中，右键单击新添加的数据流任务，再单击“重命名”，将名称更改为 Extract Sample Currency Data。
好的做法是为添加到设计图面的所有组件提供唯一的名称。考虑到易用性和可维护性，名称应说明每个组件执行的功能。按照这些命名指南，Integration Services 包可以进行自我说明。另一个说明包的方法是使用批注。有关批注的详细信息，请参阅在包中使用批注。
添加并配置平面文件源   
在此任务中，将向包中添加一个平面文件源并对其进行配置。平面文件源是一个数据流组件，它使用平面文件连接管理器定义的元数据来指定转换过程要从此平面文件中提取的数据的格式和结构。可以通过使用平面文件连接管理器提供的文件格式定义将平面文件源配置为从单个平面文件提取数据。
对于本教程，您将把平面文件源配置为使用以前创建的 Sample Flat File Source Data 连接管理器。
添加平面文件源组件
1.        打开“数据流”设计器，方法是双击 Extract Sample Currency Data 数据流任务或单击“数据流”选项卡。
2.        在“工具箱”中，展开“数据流源”，然后将“平面文件源”拖动到“数据流”选项卡的设计图面上。
3.        在“数据流”设计图面上，右键单击新添加的“平面文件源”，单击“重命名”，然后将该名称更改为 Extract Sample Currency Data。
4.        右键单击此平面文件源，打开“平面文件源编辑器”对话框。
5.        在“平面文件连接管理器”框中，键入或选择 Sample Flat File Data。
6.        单击“列”并验证列名是否正确。
7.        单击“确定”。
添加并配置查找转换   
配置了用于从源文件提取数据的平面文件源后，下一个任务是定义获取 CurrencyKey 和 TimeKey 的值所需的查找转换。查找转换通过将指定输入列中的数据联接到引用数据集中的列来执行查找。引用数据集可以是现有表或视图、新表或 SQL 语句的结果。查找转换使用 OLE DB 连接管理器连接到包含引用数据集的源数据的数据库。
对于本教程，您将向包中添加以下两个查找转换组件并对其进行配置：
•        一个转换是根据平面文件中匹配的 CurrencyID 列值对 DimCurrency 维度表的 CurrencyKey 列中的值执行查找。
•        一个转换是根据平面文件中匹配的 CurrencyDate 列值对 DimTime 维度表的 TimeKey 列中的值执行查找。
无论在哪种情况下，查找转换都将使用前面创建的 OLE DB 连接管理器。
添加并配置 CurrencyKey 查找转换
1.        在“工具箱”中，展开“数据流转换”，然后将“查找”拖动到“数据流”选项卡的设计图面上。
2.        单击 Extract Sample Currency Data 平面文件源，并将绿色箭头拖动到新添加的“查找”转换中，以连接这两个组件。
3.        在“数据流”设计图面上，右键单击新添加的“查找”转换，单击“重命名”，然后将该名称更改为 Lookup Currency Key。
4.        双击 Lookup Currency Key 转换。
5.        在“查找转换编辑器”对话框的“OLE DB 连接管理器”框中，确保显示 localhost.AdventureWorksDW。
6.        在“使用表或视图”框中，键入或选择 [dbo].[DimCurrency]。
7.        单击“列”选项卡。
8.        在“可用输入列”面板中，将 CurrencyID 拖放到“可用查找列”面板的 CurrencyAlternateKey 上。
9.        选择 CurrencyKey。
10.        单击“确定”。
添加并配置 DateKey 查找转换
1.        在“工具箱”中，将“查找”拖动到“数据流”设计图面上。
2.        单击 Lookup Currency Key 转换，并将绿色箭头拖动到新添加的“查找”转换中，以连接这两个组件。
3.        在“数据流”设计图面上，右键单击新添加的“查找”转换，单击“重命名”，然后将该名称更改为 Lookup Date Key。
4.        双击 Lookup Date Key 转换。
5.        在“查找转换编辑器”对话框的“OLE DB 连接管理器”框中，确保显示了 localhost.AdventureWorksDW。
6.        在“使用表或视图”框中，键入或选择 [dbo].[DimTime]。
7.        单击“列”选项卡。
8.        在“可用输入列”面板中，将 CurrencyDate 拖放到“可用查找列”面板的 FullDateAlternateKey 上。
9.        选择 TimeKey。
10.        单击“确定”。
添加和配置 OLE DB 目标   
您的包现在可以从平面文件源提取数据，并将数据转换为与目标兼容的格式。下一个任务是将已转换的数据实际加载到目标。若要加载数据，您必须将 OLE DB 目标添加到数据流。OLE DB 目标可以使用数据库表、视图或 SQL 命令将数据加载到各种 OLE DB 兼容的数据库中。
在此过程中，您将添加和配置 OLE DB 目标以使用以前创建的 OLE DB 连接管理器。
添加和配置示例 OLE DB 目标
1.        在“工具箱”中，展开“数据流目标”，并将“OLE DB 目标”拖到“数据流”选项卡的设计图面上。
2.        单击“查找日期键”转换，并将绿色箭头拖到新添加的“OLE DB 目标”上，以便将两个组件连接在一起。
3.        在“数据流”设计图面上，右键单击新添加的“OLE DB 目标”组件，单击“重命名”，然后将名称更改为 Sample OLE DB Destination。
4.        双击 Sample OLE DB Destination。
5.        在“OLE DB 目标编辑器”对话框中，确保已在“OLE DB 连接管理器”框中选中 localhost.AdventureWorksDW。
6.        在“表或视图的名称”框中，键入或选择 [dbo].[FactCurrencyRate]。
7.        单击“映射”。
8.        确保输入列已正确映射到目标列。
9.        单击“确定”。
测试 Lesson 1 教程包   
在本教程中，已经完成了下列任务：
•        创建了一个新的 Microsoft SQL Server 2005 Integration Services (SSIS) 项目。
•        配置了包连接到源数据和目标数据所需的连接管理器。
•        添加了一个数据流，该数据流从平面文件源提取数据，对数据执行必要的查找转换，并为目标配置数据。
包现在已经完成了！该对包进行测试了。
运行 Lesson 1 教程包
1.        在“调试”菜单上，单击“启动调试”。
包将开始运行，结果有 1097 个行被成功添加到 AdventureWorksDW 中的 FactCurrency 事实数据表中。
2.        当包运行完毕后，在“调试”菜单上，单击“停止调试”。