当前位置： >>
SQL Server SSIS 学习指南
SSIS SSIS 其全称是 Sql Server Integration Services ，是 Microsoft BI 解决方案 的一大利器. SSIS 的体系结构主要由四部分组成：Integration Services 服务、Integration Services 对象模型、Integration Services 运行时和运行时可执行文件以及封装 数据流引擎和数据流组件的数据流任务(如图)：这是我们初学者必须要了解的，只要明白了这个体系统结构，体会了各组成部分 之间的关系，清楚了什么是控制流、什么是数据流，SSIS 学起来就不难了。接下来让我们从一个例子来初探一下 SSIS 的使用.当然要想成功执行示例.你需要 在安装 Sql Server2008 的时候,选中安装 Integration Services 和 Business Intelligence Development Studio 选项 。这样 SSIS 开发平台将于一并安装起来。 1.单击开始-&所有程序-〉Sql Server 2008-〉SQL Server Business Intelligence Development Studio。第一次打开这个开发环境的时候可能需要等待几分钟。该开 发工具和 VS 开发工具很相似。 2.打开 BIDS 以后，我们可以单击 File-&新建-〉项目。如图3.在“新建项目”对话框的“模板”窗格中， 选择“Integration Services 项目”。 然后在“名称”框中，将默认名称修改为自己的所需的名称。这个设计如下。4.单击“确定”按钮。默认情况下，将创建一个名为 Package.dtsx 的空包。 5.创建好一个 SSIS 项目后，我们就可以在 Package.dtsx 包中的控制流、数据流 等选项卡中托放控件来设计 SSIS 包。 到此。 我们了解了 SSIS 体系结构以及如何利用 BIDS 创建 SSIS 包。 以后系列文章， 我将逐步介绍各个控件的使用。SSIS 包的开发 在上一章节中我们初步了解了 SSIS 体系结构以及如何创建一个 SSIS 包。 现在就 介绍一下如何在创建的包中使用各个选项卡。打开上一章节创建的 SSIS 包。整 个界面风格如下:在整个包中包含了控制流选项卡、数据流选项卡、事件处理程序选项卡等等。下 面通过一个简单的例子介绍如何利用这些选项卡创建一个完成的工作任务包。 本示例的主要功能是从数据库 AdventureWorks 中 Product 表中读取 2001 年到 2007 年的数据导入到 Excel 中。 1. 创建变量 从图左边的变量或从 SSIS 菜单中选择变量来的打开变量（Variables）按 图创建两个日期变量再图中,我们将变量 StartDate 的值设置为&&,EndDate 的值设置 为& &2. 创建连接管理器 在创建完变量后， 接着创建一个连接管理器。右击程序包编辑器底部的连接 管理器窗口的空白处，选择新建 OLE DB 连接，弹出如下对话框在图中选择新建按钮。弹出如下图信息。然后根据图中的选项配置数据库信息。 最后单击确定保存该连接。然后在单击确定按钮。最后在连接管理器上创建一个如下数据库连接你可以右击新建的 LocalHost.AdventureWorks.sa 连接管理器并重命名为 AdventureWorks。然后右击程序包编辑器底部的连接管理器窗口-〉新建连接-〉Excel 文件的连接 管理器-〉添加按钮。弹出对话框。然后在对话框中做如下设置。最终单击确定按钮。然后修改名称为 Product. 3 .使用控制流 控制流选项卡是执行程序包的工作流的地方。 在本示例中我们从工具中将数据流 任务（Data Flow Task）控件拖放到控制流选项卡中。如图所示。这个 Data Flow Task 将执行数据的转换工作。4.使用数据流 双击数据流任务（Data Flow Task）就会打开数据流（Data Flow）选项卡。下 图显示了程序包中的数据流选项卡，尚未拖放控件。从工具箱中拖出一个 OLE DB 源，命名为 Products。双击改源进行配置，如图所 示。 确保指向 AdventureWorks 连接管理器。 并将数据访问模式项改为 SQL 命令。 在 SQL 命令文本窗口中输入下列命令。 select * from Production.Product where SellStartDate&? and SellStartDate&?问号表示通过变量传递的参数值。单击参数按钮将问号映射到已创建的变量。在 设置查询参数窗 口中看到每个问号参数都是有序的。因此第一问号对应 Parameter0，第二个问号对应 Parameter1 。依次类推。了解参数配置后，我们 将 Parameter0 映射到 User::StartDate,Parameter1 映射到 User::EndDate。 如 图：单击确定按钮返回到数据流选项卡中。接下来在工具箱中的数据流目标中将 Excel 目标控件拖放到设计窗格上。通过从数据源拖动绿色箭头到该 Excel 目标 源。然后右键单击 Excel 目标源选择编辑，弹出对话框，接着按图下步骤设置信 息。
设置成功后，整个数据流选项卡的流程如下：5.执行包 包创建成功后，现在我们来执行一下，看看效果如何。右键单击 Package.dtsx 包-〉选择执行包选项。将会看到如下结果图途中绿色表示包创建成功并执行完成。打开导入的 Excel，我们发现已经存在数 据。到此一个简单的包流程设计完成。后续我们将如何使用包中各个选项卡。SSIS 包的组建之连接管理器 上一篇我们通过一个示例来介绍一下 SSIS 包的开发.接下来的内容我们将学习 一下包中各个选项卡的使用.如:连接管理器选项卡、控制流选项卡、数据流选项 卡和事件处理选项卡等等。这一篇将介绍一下连接管理器作用以及使用情况。 连接管理器的作用是连接到不同类型的数据源以提取和加载数据。 任何包的开发 都需要提供源数据。 下表列出了 SQL ServerIntegration Services 提供的连接管理器类型。 类型 ADO ADO.NET CACHE EXCEL FILE FLATFILE FTP HTTP MSMQ MSOLAP100 MULTIFILE MULTIFLATFILE OLEDB ODBC SMOServer SMTP SQLMOBILE WMI 说明 连接到 ActiveX 数据对象 (ADO) 对象。 使用 .NET 提供程序连接到数据源。 从数据流或从缓存文件 (.caw) 中读取数据，并可将 数据保存到缓存文件。 连接到 Excel 工作簿文件。 连接到文件或文件夹。 连接到单个平面文件中的数据。 连接到 FTP 服务器。 连接到 Web 服务器。 连接到消息队列。 连接到 SQL ServerAnalysis Services 实例或 Analysis Services 项目。 连接到多个文件和文件夹。 连接到多个数据文件和文件夹。 使用 OLE DB 访问接口连接到数据源。 使用 ODBC 连接到数据源。 连接到 SQL Server 管理对象 (SMO) 服务器。 连接到 SMTP 邮件服务器。 连接到 SQL Server Compact 数据库。 连接到服务器，并指定服务器上 Windows Management Instrumentation (WMI) 管理的范围。下面就简单的介绍一下我们常用的使用连接管理器类型。?OLE DB 访问接口连接到数据源(数据库连接)1. 首先打开上一篇创建名为&SSISDemo&项目. 2. 在连接管理器窗口中，右击选择 New OLE DB Connnection 项，将弹出如 下窗口:左边数据连接框显示的连接是我们已创建好的数据库连接。 右边数据库连接属性 框显示对应的属性信息。你也可以新建一个数据库连接。单击新建按钮，弹出如 下对话框在这对话框中，我们可以根据自己的需要填写相应的内容。本例是以连接本地 AdventureWorks 数据库。单击确定按钮。则会在连接管理器看到如下信息:图中 LocalHost.AdventureWorks.sa 就是我们刚才创建的数据库连接。右击 LocalHost.AdventureWorks.sa 弹出属性框。可以根据需要修改这些属性。比如将 Name 属性修改为: AdventureWorks 。 到此一个 简单 OLE DB 连接方式建立 完成。其中的一些属性含义。自己可以在后续的学习中逐步的掌握。?FILE 连接到文件或文件夹(平面文件连接)平面文件连接管理器要比 OLE DB 连接方式要复杂的多。平面文件连接方式主 要是连接非数据库类型的文件。 下面介绍下如何创建平面文件连接。假如我们有 一个名为 User.txt 文件。数据格式如下:每行有两列数据。已“Tab”隔开。接下来介绍如何在连接管理器中连接这个文 本文件。 1. 仍然在连接管理器框中右击，选中新建平面文件连接，弹出对话框在图中可以看到配置平面文件连接管理器需要配置连接管理器名称、常规、列、 高级、预览这个属性。下面将介绍如何配置这些属性? ?连接管理器名称:为工作流中的平面文件连接提供唯一的名称。所提供的 名称将在连接管理器框中显示。 常规选项卡:使用“平面文件连接管理器编辑器”对话框的“常规”页可 以选择文件和数据格式。 使用平面文件连接可以将包连接到文本文件。该 选项卡中包含以下属性：文件名:键入要在平面文件连接中使用的路径和 文件名。 区域设置:在区域设置下拉框中选中指定的区域位置，以便为排序以日期 和时间格式提供语言特性的信息。 在设计的时候最好选择英语(美国)选项 Unicode 复选框:指示是否使用 Unicode。 如果使用 Unicode 则不能指定代 码页。 代码页：在代码页中选中指定非 Unicode 文本的代码页。设计时最好选择 1252（ANSI-拉丁语 I）格式:在格式下拉框中选中文本的格式属性说明带分隔符 各列之间由在“列”页上指定的分隔符隔开 固定宽度 列的宽度固定 右边未对 在右边未对齐的文本中，除最后一列之外的每一列的宽度都相同。它有行分割 齐 符分割文本限定符:指定要使用的文本限定符。例如，可以指定文本字段必须用 引号括起来。若选择文本限定符之后，就不能重新选择&无&选项，键入 None 以 取消选择文本限定 符。 标题行分隔符：从标题行的分隔符列表中选择，或输入分隔符文本。值 {CR}{LF} {CR} {LF} 分号 {;} 冒号 {:} 逗号 {,} 制表符 {t} 竖线 {|} 说明 标题行由回车符和换行符的组合分隔。 标题行由回车符分隔。 标题行由换行符分隔。 标题行由分号分隔。 标题行由冒号分隔。 标题行由逗号分隔。 标题行由制表符分隔。 标题行由竖线分隔。要跳过的标题行数： 指定要跳过的标题行数或初始数据行数 （如果有的话） 。 在第一个数据行中显示列名称： 指示在第一个数据行中是否要求列名或提供 列名。 根据常规选项卡中的这些属性以后，我们就可以根据需求设定文本显示的格式。 如下图?列选项卡： 使用”平面文件连接管理器编辑器”对话框中的”列”选项卡 可以在这里设置行和列的信息。并预览相应的文件。如下图:包含如下属性 行和列分隔符:此属性和常规选项卡中的标题行分隔符一样。可以根据需求设置 行和列的显示方式。 预览: 查看平面文件中的示例数据，这些数据已按所选的选项划分为列和行.如 上图。 刷新:通过单击“刷新”查看更改要跳过的分隔符后的效果。只有在更改行或列 选项之后，此按钮才可见。 重置列:通过单击“重置列”可以删除除原始列之外的所有列。 只有调转到其他 选项卡后，然后再回到“列”选项卡，此按钮才可见。 本示例我们在列分隔符选项中选择制表符（t）,然后单击刷新按钮。?高级选项卡： 使用“平面文件连接管理器编辑器”对话框的“高级” 页， 设置指定 Integration Services 如何读写平面文件中的数据的属性。可 以更改平面文件中各个列的名称， 并设置包括文件中每个列的数据类型和 分隔符在内的属性。默认情况下，字符串列的长度为 50 个字符。可以调 整这些列的长度， 以免数据截断或超出列宽。还可以更新其他元数据以便 与目标列兼容。 例如，可以将只包含整型数据的列的数据类型更改为数值 数 据类型，例如 DT_I2。可以手动进行这些修改，也可以单击“选择类 型”按钮， 以使用“提供列类型建议”对话框来评估示例数据并自动进行 其中一些更改。在高级选项卡中 我们可以做如下工做：配置各列的属性： 选择左窗格中的列可在右窗格中查看列的属性。 请参阅下 表以了解数据类型属性的说明。 列出的部分属性仅对某些平面文件格式是可配置 的。属性 ColumnType 说明 表示列是由分隔符分隔、还是固定宽度，或是右边未对齐。此属性是 只读的。在右边未对齐的文件中，除最后一列之外的每一列的宽度都 固定。它由行分隔符分隔。 OutputColumnWidth 指定值存储为字节数；对于 Unicode 文件，此值对应于字符数。在 数据流任务中，此值用于设置平面文件源的输出列宽。 注意 在对象模型中，此属性的名称为 MaximumWidth。 DataType TextQualified 从可用数据类型的列表中进行选择。 指示文本数据周围是否有文本限定符（例如引号字符）。 值说明 True 平面文件中的文本数据是受限定的。 False 平面文件中的文本数据是不受限定的。 Name DataScale ColumnDelimiter 提供说明性列名。如果不输入名称，则 Integration Services 将自 动创建名称，格式为“列 0”、“列 1”，依此类推。 指定数字数据的小数位数。小数位数是指小数点后的位数。 从可用列分隔符的列表中进行选择。选择不可能出现在文本中的分隔 符。对于固定宽度的列，将忽略此值。 值说明 {CR}{LF}列由回车符和换行符的组合分隔。 {CR}列由回车符分隔。{LF}列由换行符分隔。 分号 {;}列由分号分隔。 冒号 {:}列由冒号分隔。 逗号 {,}列由逗号分隔。 制表符 {t}列由制表符分隔。 竖线 {|}列由竖线分隔。 DataPrecision 指定数字数据的精度。精度是指数字的位数。 数。对于分隔列，将忽略此值。 注意 在对象模型中，此属性的名称为 ColumnWidth。 InputColumnWidth 指定值以字节数进行存储；对于 Unicode 文件，该值将显示为字符新建：通过单击“新建”添加一个新列。默认情况下，单击“新建”按钮将 会在列表末尾添加新列。该按钮还包括以下选项，可以在下拉列表中选择。值 添加列 在其前插入 在其后插入 说明 在列表末尾添加新列。 在所选列前面插入新列。 在所选列后面插入新列。删除：选择一列，然后单击“删除”来删除该列。 建议类型：使用“提供列类型建议”对话框可以计算文件中的示例数据， 并 获取关于每列的数据类型和长度的建议。 充分了解高级选项卡的属性后，我们将列 0 的 Name 和 DataTye 属性改为 CustomerID 和 DT_I8 类型。 将列 1 的 Name 和 DataTye 属性改为 SKUList 和 DT_STR 类型，并将 OutColumnWidth 属性修改为 1000。?浏览选项卡：单击浏览选项卡，可以看到如下图显示的数据只要前三个选项卡配置成功后， 预览一下数据显示格式就可以了。最后单击确定 按钮。 平面文件连接管理器就建立成功。 最终在连接管理器框中显示刚才创建名 为“Userout.txt“的平面文件管理器。 本节就介绍常用的两个连接管理器。 其他的连接管理器可以参考这两个例子自己 尝试去建立连接。SSIS 的控制流之 For 循环容器 SSIS 包由一个控制流以及一个或多个数据流（可选）组成。下面的关系图显示具有一个容器和六项任务的控制流。 这些任务中有五项定义于包级别，还有一项定义于容器级别。任务位于容器内。 在控制流中的工具箱.我们可以将工具箱中的控件分为容器控件和任务控件两类。 现在我们先了解一下容器包含哪些控件 容器 1.For 循环 For 循环容器类似于编成语言中的 For 循环结构。循环每次重复时，For 循环容 器都计算一个表达式并重复运行其循环， 直到表达式计算结果为 False 才结束次 循环。 For 容器使用很简单。这里就简单介绍一下使用方法。 &BIDS 开发工具打开创建的“SSISDemo”项目，然后创建一个新的包。命名为 “ForContainerDemo” &在工具左边选中变量选项卡，如果没有该选项，则可以在菜单上选中”SSIS” 菜单,然后单击变量。弹出如下图:在这里我们可以自己创建变量。单击上图中的第一个图标。创建一个变量。创建 好后，可以设置其数据类型，初始值。结果如下图：你也可以单击第二个图标，删除其变量。 &选中控制流选项卡后，在左边将出现工具选项卡，打开选项卡，将 For 循环容 器拖到控制流中。然后再将一个脚本任务控件拖放到 For 容器中如图:&右击 For 容器，单击编辑，弹出对话框我们将看到如下属性 InitExpression :提供初始化该循环所用值的表达式（可选）。 EvalExpression :提供用于计算循环应停止还是继续的表达式。 AssignExpression :提供在每次循环重复时更改条件的表达式（可选）。 Name :为 For 循环容器提供唯一的名称。此名称用作任务图标中的标签。 Description :提供 For 循环容器的说明。 依据这些属性按如下图设置。其中 Count 变量就是我们在变量容器中设置的变量， 在使用变量时在其前面加上 “@”。单击确定按钮即可 &拖放一个脚本任务控件到 For 容器中，然后右击脚本任务-〉选择编辑。弹出对 话框。 ReadOnlyVariables 属性中单击右边的“?”按钮， 在 弹出选择变量对话 框。在这对话框中我们选中自己定义的 Count 变量。配置结果如下:&接着单击图上的编辑脚本按钮。 弹出 VS 编辑器。 main 函数中加入如下代码: 在 string Count = Dts.Variables[&Count&].Value.ToString();MessageBox.Show(Count); 然后关闭 VS 编辑器。最后关闭脚本任务编辑器 &右键单击“ForContainerDemo”包，选择执行包，结果如图:我们发现弹出五次对话框。说明 For 容器循环了 5 次。最终执行结果如下。到此，For 循环容器控件介绍完成。接着我们介绍下一个 Foreach 循环容器。相 信大家看到 Foreach 字词。估计也猜出如何使用该控件了吧。SSIS 的控制流之 Foreach 循环容器和序列容器 上一篇介绍了 For 循环容器的使用。 本篇将介绍 Foreach 循环容器和序列容器的 使用。 Foreach 循环容器 Foreach 循环容器定义包中的控制流。其循环的实现类似于编程语言中的 Foreach 循环结构。SQL Service Integration Services 为 Foreach 容器提供了 一下几种枚举类型枚举器 Foreach ADO Foreach ADO.NET 架构行集 Foreach 文件 Foreach 源变量 Foreach 项 Foreach Nodelist Foreach SMO 配置要求 指定 ADO 对象源变量和枚举器模式。 指定与数据库的连接和要枚举的架构。 指定要枚举的文件夹和文件、检索到的文件的文件名 格式，以及是否要遍历子文件夹。 指定包含要枚举的对象的变量。 定义 Foreach 项集合中的项，包括列和列数据类型。 指定 XML 文档的源并配置 XPath 操作。 指定与数据库的连接以及要枚举的 SMO 对象。下面看看具体如何使用该控件。假如我们在 D:\SampleData 含有如下文件:现在我们用 Foreach 容器遍历这些文件。 &创建一个名为”ForeachContainerDemo”的包。然后定义一个变量 FileName， 类型设置为 String。 &按如下图拖放控件&右击”遍历货币”单击编辑。按如下图配置信息其中 文件属性中设置&.txt&表示 读取指定目录下的所有 txt 文件。然改为 a.txt 表示读取目录下指定的文件。 &单击变量映射选项,配置如下:该配置表示，每次遍历的文件名赋值给变量 FileName.然后点击确定。至此 Foreach 容器配置完毕。 &配置脚本任务和上一篇配置相同。只是做如下改动即可。修改 ReadOnlyVariables 属性值为“User::FileName”,并在 Main 函数中写下如下代 码。 string fileName = Dts.Variables[&FileName&].Value.ToString(); MessageBox.Show(fileName); &执行包。将遍历所以文本文件。结束后的效果如下:序列容器 序列容器是一种十分简单， 但使用非常广泛的容器，它可以对控制流的任务流组 件进行结构化处理， 把一些业务相关的任务组件， 放到一个序列容器中， 使整 个 包看起来更加整洁、美观，就如同我们家里的书柜、衣柜似的，把不同种类的东 西整理在里面，收藏起来，既美观，又易于取用。它是将一个或多个控件作为一 个 工作单元来处理。例如:在图中名为“Prepare New DataBase”的序列容器包含三个执行 Sql 任务的控件。 是将这三个控件组合成一个控件。只有三个控件都执行成功后，才能继续执行下 面的流程。序列容器的作用就是这些。不需要配置，也不要编写代码。只要将控 件拖放到序列容器即可。有点类似我们 C# 中用户控件一样。 到此本篇的内容就结束。 主要是介绍 For 容器控件的使用。顺便介绍一下序列容 器控件。SSIS 之序列容器 序列容器是一种十分简单， 但使用非常广泛的容器，它可以对控制流的任务流 组件进行结构化处理，把一些业务相关的任务组件，放到一个序列容器中，使整 个包看 起来更加整洁、美观，就如同我们家里的书柜、衣柜似的，把不同种类 的东西整理在里面，收藏起来，既美观，又易于取用。 比如，在一个 Integration Sevices 包中，包含有财务数据、业务数据。整 个包中的任务组件会非常多， 如果不对它们进行分组管理，整个包看起来就会比 较乱，非常难于管理与维护。 相反如果我们将处理财务数据的任务，放入一个 序列容器中，将处理业务数据的任务放入另一个序列容器，整个包看起来，就会 一目了然， 哪些任务是处理财务数 据， 哪些任务是处理业务数据了， 非常清楚。 如图：不仅如此， 如果点击右上角象箭头一样的符号， 还可才把这些容器折叠起来， 整个包就更加清楚明了，一目了然了。如图：
SSIS 之数据流任务 数据流任务主要用于运行数据流以提取、转换、加载源数据。一个包中可以有 多个数据流任务， 也可以包含零个数据流任务。下面关系图显示含有一个数据流 任务的包：了解数据流任务后，我们就简要的学习一下如何创建数据流任务。 1. 在工具箱中双击数据流任务控件或则将其拖放到控制流页面中。如图:你可以根据需求来修改数据流任务的属性。 2. 单击数据流选项卡，从工具箱中拖放一个 OLE DB Source.命名为 Products。 右击选择编辑进行配置。如图所示。其中 sql 语句的问号表示通过变量传递的参数值。 单击参数按钮将问号映射到创建的变量。如图:你也可以通过变量属性的下拉框中选择新建变量选项创建新的变量。 然后单 击确定按钮回到父选项框,再然后单击确定按钮 3. 在工具箱将 Lookup 查找控件拖放到数据流中，通过从数据源拖动绿色箭头 到该转换，将 Products 源和 Lookup 转换相连接。将 Lookup 转换命名为 Find Model name。然后双击该控件，做如下配置: 3.1 常规选项卡配置如下:3.2连接配置如下3.3列配置如下3.4高级配置如下 如果在常规选项卡中选择完全缓存模式，则该选项卡不能使用。3.5错误输出配置如下这样一个完整的 Lookup 转换就配置结束。这个已完成的配置的转换现在的做法 是， 试图根据数据源中的 ProductModelID 匹配表 ProductModel。如果无匹配的 行，就会发生错误，不过，因为已经配置该转换忽略这一错误，因此如果没有找 到匹配的。ModelName 列将包含 Null 值。 4. 将一个派生列和行计数控件拖放到数据流中，分别命名为 Assign Default Values And Prie、No Mapping Count，并将 Lookup 转换的输出连接到该派生列 行计数控件。如下图:打开派生列的编辑框。配置如下信息配置行计数信息如下5.经过对源数据的转换后， 将这些数据加载到目标数据中。可以将行计数拖放到 设计窗格。连接派生列到行计数，将行计数命名为 Mapping Count。双击它并配 置如下信息:将组建选项卡中的 VariableName 属性设为 IntCount，当然这个变量事先要在变 量工具箱中设计好。?在工具箱中的数据流目标卡中，托动一个平面文件目标。命名为 Partner Extract。右击单击编辑，选择新建按钮，弹出对话框然后单击确定按钮，弹出下面的对话框剩下的配置， 按连接管理器配置方式进行配置。在这我们是将转换后的数据输出 到： E:\BI 资料\SSISETLDemo\partnerextract.txt 中 到此一个完整的数据流任务就建立完成。整体流程如下运行该包。得到如下信息
SSIS 数据转换组件_聚合转换 聚合转换功能类似于 sql 语句中的聚合函数。它应用列值，并将结果作为新列 复制到转换输出中。聚合转换支持下列运算功能 运算 Group by Sum Average Count Count distinct Minimum Maximum 说明 将数据集划分为组。任何数据类型的列都可用于分组。 对列中的值求和。只能对数值数据类型的列求和。 返回列中值的平均值。只能对数值数据类型的列求平均值。 返回组中项目的数量。 返回组中的唯一非空值的数量。 返回组中的最小值。 返回组中的最大值。添加并配置聚合转换， 则包必须已含有至少一个数据流任务和一个数据源。这里 已一个简单的例子来介绍一下如何配置聚合转换。本例子的内容为:聚合每个订 单的总金额。如果用 T-SQL 的话也就一个语句就可以了: select SalesOrderID,SUM(OrderQty*UnitPrice) amount Sales.SalesOrderDetail 现在介绍通过聚合转换如何获取同样的结果。 1. 在 BIDS 中，打开包含所需包的 Integration Services 项目。 2. 在解决方案资源管理器中的 SSIS 包文件创建一个名为 AggrationDemo 的 包，可以看到如下结果 from1. 在 AggregationDemo 包控制流选项卡中拖放一个数据流任务，如图1. 在连接管理器中窗口中，创建一个 OLEDB 连接器连接本地数据库 AdventureWorks、一个平面文件连接器连接 C:\Users\zhuyujing\Desktop\test.txt。如图：1. 在数据流选项卡中,托动一个 OLE DB 源、派生列、聚合转换以及目标平面 文件。如图:各个控件配置如下：?OLE DB 源?派生列?聚合转换其中在比较标志列表中有四个复选框。表示的含义如下: 比较选项 说明 忽略大小写 指定比较是否区分大小写字母。如果设置了此选项，字符串比较会 忽略大小写。例如，&ABC& 和 &abc& 没有区别。 忽略假名类 指定比较是否区分日语的两种假名字符类型：平假名和片假名。如 型 果设置了此选项，字符串比较会忽略假名类型。 忽略字符宽 指定比较是否区分字符的单字节形式和该字符的双字节形式。 如果 度 设置了此选项， 字符串比较将把同一字符的单字节形式和双字节形 式视为相同。 忽略非空格 指定比较是否区分空格字符和标注字符。如果设置了此选项，则比字符较会忽略标注字符。例如，&?& 与 &a& 相同。?目标平面文件配置好控件阿任务后，执行该包。执行结果如下:打开目标 txt 文本，看看执行的结果
SSIS 数据转换组件_派生列、审核、字符映射转换和条件性拆分转换一派生列派生列转换通过对输入列进行类型转换或应用表达式得出新的结果。 这个结果可 以作为新列添加，也可以替换输入列。可以使用此转换执行下列任务： 1.将不同列的数据连接到一个派生列中。例如，可以使用表达式 FirstName + & & + LastName 将 FirstName 和 LastName 列中的值组合到名为 FullName 的单个派 生列中。 2.通过使用 SUBSTRING 之类的函数从字符串数据中提取字符，然后将结果 存储到派生列中。例如，可以使用表达式 SUBSTRING(FirstName,1,1) 从 FirstName 列提取人名的首字母。 3.对数值数据应用数学函数，然后将结果存储到派生列中。例如，可以使用 表达式 ROUND(SalesTax, 2) 将数值列 SalesTax 的值更改为精确到小数点后 两位。 4.创建比较输入列和变量的表达式。 例如， 可以使用表达式 ProductVersion == @Version? ProductVersion : @Version 来比较变量 Version 与 ProductVersion 列中的数据，然后根据比较结果决定选用 Version 还是 ProductVersion 的值。 5.提取日期时间值的某部分。例如，可以通过表达式 DATEPART(&year&,GETDATE()) 使用 GETDATE 和 DATEPART 函数提取当前年份。 在上篇聚合转换文章中我们已经使用到了派生列。其配置如下:在这图中， 可以根据需求来进行一些设计。本图的设置为计算单个商品购买的总 金额。二 审核转换审核转换控件配置很简单， 它使得包中的数据流包含有关包运行所处环境的数据。 例如可以将包的名称、 计算机名称和操作员姓名添加到数据流中。该转换功能只 以下系统变量。 值 执行实例 GUID 包 ID 包名称 版本 ID 执行开始时间 计算机名称 用户名 任务名称 任务 ID 说明 插入唯一标识包的执行实例的 GUID。 插入唯一标识包的 GUID。 插入包名称。 插入唯一标识包版本的 GUID。 插入包执行的开始时间。 插入启动包的计算机的名称。 插入启动包的用户的登录名。 插入与审核转换相关联的数据流任务的名称。 插入唯一标识与审核转换相关联的数据流任务的 GUID。配置该转换功能如下步骤。1. 将审核转换控件按拖放到数据流任务中， 并将上游的控件用绿色箭头连接 起来。如图:1. 右键单击审核转换弹出窗口1. 单击审核类型下拉框。 选中需要的系统变量。然后在输出列中设置输出的 名称。得到如下结果:配置好审核的系统变量， 单击确定即可完成审核转换功能的配置信息。整个流程 如下:将转换的数据存放到 C:\Users\zhuyujing\Desktop\test.txt。执行包.得到的 结果如下:三字符映射表转换字符映射表转换其实就是将字符串函数应用于输入列中的字符数据。 该功能只对 字符串数据类型的输入列执行。例如我们可以将字符转换为下写、大写、或则繁 体等等。如下图所示现在我们就演示一下将输入列中的 NewName、PackageName 分别转换为繁体和大 写的，并将输入列 NewName 用转换后的繁体替换，创建一个新的列 NewPackageName 用于存储输入列 PackageName 映射的繁体字符。 整个流程如下：配置成功后执行包。可以看到没有转换之前,NewName 和 PageageName 分别是简 体和小写字符转换后，NewName 列中的数据变成繁体、packageName 列对应的 NewPackageName 列都是大写字符。四 条件性拆分转换条件性拆分转换功能类似 C#编程语言中的 swith?case 语句。此转换将计算 表达式， 并且根据计算的结果将数据行定向到不同输出中。此转换还提供一个默 认输出,如何某个行与任何表达式都不匹配。则它将被定向到默 认输出。例如， 可以将销售订单总额在
之间的数据输出到一个路径， 将销售订单总额 在
之间的数据输入到一个路径，将销售 订单总额&=7000 的数据输出 到一个路径。不满足以上条件的设置为默认条件。作为另外一条路径输出。在设 置这些条件的同时还可以编辑输出列的名 称。最终结果如下图:配置好以后单击确定即可。 为了检测结果。我们先在变量选项卡中设置如下几个 变量然后在数据流选项开中托动四个行计数转并修改名称。如图:右键单击其中一个行计数，弹出窗口后，按如下图设置 VariableName 属性。本 图已名为 BadRowCount 为例。其他行计数转换分别按同样的操作设置 VariableName 即可。然后将刚才的条件性拆分转换与其中一个行计数用绿色箭头连接。 弹出如下窗口在输出下拉框中选中条件。然后单击确定按钮即可。如下图其他三个行计数转换按此步骤操作即可。最总得到如下图:执行该包，等到如下图所示的结果
SSIS 数据转换组件_复制转换、数据转换、行计数一 复制转换复制列转换是一种很简单的转换机制。 它主要是将一列或多列地数据复制一 份。 然后后续的工作可以对复制的列中的数据做任何操作。而不影响被复制列的 数据。下面我们接着上一篇的条件性拆分的包来介绍如何使用复制转换。 将复制列转换从工具箱中 托动到数据流选项卡中。并于 ExcelSaleOrderCout 行计数连接。连接方向是行计数指向复制列转换。双击打 开复制列，弹出编辑界面，选择要拷贝的列，并可以重命名。结果如下图:到此复制列转换工具配置完成， 接下来你可以从工具箱中拖动任何控件与之连接。 使用复制的列。二 数据转换(Data Conversion)数据转换也是一个比较简单的转换组件， 它是将输入列的类型转换为其他类型并 作为新的列输出。 可以对单个输入列应用多个转换。下面让我们一起了解如何使 用该功能。 1.打开 Intergration Service 项目，创建名为”DataConversionDemo”的包。 并在控制流中拖放一个数据流任务组件。如图:2.在连接管理器中新建一个 OLE DB 连接，连接数据库 AdventureWorks.创建成 功后结果如下图:3.在数据流选项卡中托动一个 OLE DB 源和一个数据转换任务。并将连接线（绿 色或红色箭头） 从源或前一转换拖动到数据转换， 从而将数据转换连接到数据流。 4.双击数据转换，弹出窗口。选择需要转换的列，在 DataType 下拉列表中选择 需要的数据类型。Output Alias 栏内设置输出时使用的别名。5.单击确定按钮，数据转换功能配置完成。三 行计数行计数转换在行通过数据流时对行进行计数， 并将最终计数结果存储在一个 变量中。在使用行计数控件之前我们需要创建一个自定义变量。 1.在菜单栏中单击“SSIS(S)”选项， 然后在其下拉框中选中“变量”选项。 弹出如下窗口:2.单击添加变量按钮， 如图中带有红色圆圈的按钮。此时在会增加一个变量 如图:将名称中的“变量”改为:& Count&.到此用户自定义变量增加完成。如果想 删除用户自定义的变量，则选中要删除的变量，然后单击图中带有红色“X”的 按钮即可。 3.拖放一个行计数转换到 “DataConversionDemo”包中，并与数据转换连 结，箭头指向行计数。右键单击行计数，然后选择“编辑”选项，在弹出的窗口 中将 VariableName 设置为 User::Count。单击确定按钮，行计数配置完毕。 如果想在执行包的过程中想查看一下利用数据转换后的数据。 我们需配置一个数 据查看器。 右键单击 数据转换和行计数转换中的连接，选择”数据查看器“，弹出如下对 话框:然后选择“数据查看器”，在右边单击添加按钮，弹出如下图框:在“常规”选项卡中， 选中“网格”， 单击确定按钮。 数据查看器配置完成， OK， 整个包的配置如下:到此，我们学会如何使用复制转换、数据转换、行计数三种转换功能，同时在本 文中也介绍了如何添加用户自定义变量以及数据查看器。SSIS 数据转换组件_导出、导入转换一 导出转换组件导出列转换读取数据流中的数据,并将数据插入到指定的文件中.次转换使 用成对的数入列:一列是要输出的数据,一列是将这些数据输出到哪些文件 里. 转换在处理时,数据将插入到指定的文件中.如果这些文件不存在,转换将创建这 些文件,然后将数据写入到文件中.要写入的数据必须具有 DT_Text、 DT_NTEXT 或 DT_IMAGE 数据类型。 下面的例子中将介绍如何使用该转换工具。本例演示如何将数据库 AdVentureWorks 中 Production.Document 表中的 Document 列导出到文件中。 1. 创建一个文件夹 E:\SSIS\SSISDemo\Export，导入的文件将放在这个文件 夹中。 2. 新建一个名为 ExportColumnDemo 的包，在控制流中添加一个 Data Flow Task。然后在连接管理器中创建一个 OLEDB 连接，用于连接 AdventureWorks 数据库。如图:1. 打开数据流界面，添加一个 OLE DB，连接到 LocalHost.AdventureWorks 中的 production.document 表。 预览这个表可以看到有一个列 FileName， 他们指定了文件的存储路径。在导出数据时，我们可以把 Document 列数 据导出到 E:\SSIS\SSISDemo\Export1. 添加一个派生列转换将与刚创建的数据源连接起来。前头指向派生列。编 辑派生列。按如图黄色标记设置:1. 添加一个到出列转换，将派生列与它连接起来。然后双击导出列， 弹出 的窗口。 在提取列中选择 Document 列， 应为在表[Production].[document] 中这一列是文件列，在文件路径列中选择 NewPath，因为这一列是通过派 生列产生的文件路径。下面详细了解一下窗口中的允许追加、强制截取以及编写字节顺序列的含义:列名 允许追加 强制截取 编写字节顺序说明 指定转换是否将数据追加到现有文件中。默认 false 指定转换在写入数据之前是否删除现有文件的内容。默认值为 false 指定是否将字节数序标记(BOM)写入文件。 只有在数据具有 DT_TEXT 或 DT_NTEXT 数据类型，并且未将数据追加到现有数据文件时，才 会写入标记(BOM)1. 执行包，如图 1-1，可以看到文件夹中已经有 9 个文件。见图:1-2图 1-1图 1-2 1. 当我们重新设置导出列的属性时，按如下图设置:然后执行该包，结果会报错如图:在 执行结果中看到如下错误信息 [Export Column [37]] 错误: 无法打开要执行写操作的文件 “E:\SSIS\SSISDemo\Export\Crank Arm and Tire Maintenance.doc”。该文件 存在且不能被覆盖。 如果 AllowAppend 属性为 FALSE，并且 ForceTruncate 属 性被设置为 FALSE， 则存在该文件将会导致此失败。如何避免这些错误可以参考 以下内容:追加 False True False True截断 False False True True文件存在 否 否 否 否False False 是 False True 是 True False 是 True True 是结果 该转换将创建一个新文件并将数据写入到该文件中。 该转换将创建一个新文件并将数据写入到该文件中。 该转换将创建一个新文件并将数据写入到该文件中。 该转换的设计时验证失败。将两个属性都设置为 true 是无效的。 发生运行时错误。文件存在，但转换无法写入到文件中。 转换将删除文件，然后重新创建文件并将数据写入到文 件中。 转换将打开文件并将数据写入到文件末尾。 该转换的设计时验证失败。将两个属性都设置为 true 是无效的。二 导入列转换导入列转换与导出列转换正好相反,它是从文本中读取数据并将数据添加到数据 流中的列中.通过此转换,包可以将存储于各个单独文件中的文本和图像添加到 数据流中.使用导入列转换要求输入列得数据类型必须是 DT_TEXT、DT_NTEXT 或 DT_IMAGE. 现在来做一个例子介绍一下如何将图像文件导入到 AdventureWorks 数据库中。 1. 创建路径 E:\BI 资料\IntegrationServices\ImportColumn，然后在该文 件中任意创建 5 个图片。在在该文件中创建一个名为“PictureInfo”的 txt 文本。其内容如下: E:\BI 资料\IntegrationServices\ImportColumn\1.png E:\BI 资料\IntegrationServices\ImportColumn\2.png E:\BI 资料\IntegrationServices\ImportColumn\3.png E:\BI 资料\IntegrationServices\ImportColumn\4.png E:\BI 资料\IntegrationServices\ImportColumn\5.png 自此整个文件的内容如下:2.在 AdventureWorks 数据库中执行下面的 sql 语句 CREATE TABLE [dbo].[MyImages]( [ID] [int] IDENTITY(1,1) NOT NULL, [ImageFilePath] [varchar](100) NOT NULL, [Document] [image] NOT NULL, [CreateTime] [datetime] NOT NULL, CONSTRAINT [PK__tblmyIma__B39A] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO 3.新建一个名为“ImportColum”的包，在控制流中拖放一个“Data Flow Task”，然后再连接管理器中创建一个 OLE DB 连接用于连接 AdventureWorks 数据库。如图:= ON)4 在数据流选项卡中， 拖放一个平面文件数据源。 命名为“Image Source”， 然后右键单击编辑，弹出窗口，然后再窗口中，单击新建按钮。数据源设置为: E:\BI 资料\IntegrationServices\ImportColumn\PictureInfo.txt。在常规和 高级选项中设置如下:设置成功后，可以单击预览，看一下设置结果:1. 设置好平面数据源后，托动一个导入列转换到数据流选项卡中，命名为 “Improt Column”,并将与平面数据源连接。绿色箭头指向导入列转换工 具。双击导入列转换弹出窗口,在输入列选项卡中选中可用输入列。如下 图:1. 在刚才的弹出框中选择输入属性和输出属性选项卡,你会发现输入列集合 中有一列 ImageFilePath，但是在输出列集合中却没有列输出。此时我们 加入一个输入列名为”Document”，类型为 DT_IMAGE.如图：注意红色标 记的地方在下方我们看到一行警告信息。 解决此警告信息我们做如下操作。选中框中的名 为 ImageFilePath 的输入列。将 FileDataColumnID 属性设置为输出列 Document 的 ID 即可。如下图红色标注的地方。设置成功。警告信息消失。此时导入列转换配置成功。 1. 托动一个派生列到数据流中与导入列转换相连。箭头指向派生列。设置派 生列1. 最后添加 OLE DB Desination，将 Import Column 和 Ole DB Destination 连接起来，编辑 Ole DB Destination 设置为如上创建的表(MyImages)， 点击 Mappings setting 设置列映射1. 设置成功后执行该包:查看目标数据库中的 MyImages 表。如下:导出、导入组件功能我们学习完毕。谢谢！SSIS 数据转换组件_模糊分组转换 模糊分组转换执行数据清理工作。它首先查找可能重复的行，然后选择要在对数 据进行标准化的过程使用的规范数据行。模糊分组的任务编辑器有下面三个标 签:1.连接管理器使用列表框选择现有的 OLE DB 连接管理器，或使用“新建”按钮创建新的连接。该功能主要是为模糊分组转换算法完成工作所需而需要创建临时 SQL Server 表。创建的表可能会很大，因此在生产服务其只有有限的可用磁盘空间时，因该 考虑将模糊分组转换连接到 Sql Server 的非生产实例中。则此转换性能可能会 提高。2 列该选项卡可以设置 用于对带有重复值的行进行分组。如图:若要配置该转换， 您必须选择要在确定重复项时使用的输入列，而且必须为每列 选择匹配类型 （模糊匹配或完全匹配） 完全匹配保证只对该列中具有相同值 的 。 行进行分组。完全匹配可以应用到除 DT_TEXT、DT_NTEXT 和 DT_IMAGE 之外的 任何 Integration Services 数据类型的列。模糊匹配对具有相似值的行进行分 组。近似匹配数据的方法基于用户指定的相似性得分。在模糊匹配中，只能使用 具有 DT_WSTR 和 DT_STR 数据类型的列。 在图中最下面每列表示的含义如下: 列名 输入列 输出别名 组输出别名 匹配类型 最低相似性 说明 在可用输入列中选择要分组的输入列 为相应的输出列输入一个描述性的名称，默认为输入列名 为包含分组重复项的规范值得列输入一个描述性名称。默认 为输入列名后加_clean 两种，Fuzzy-模糊匹配，Extract-完全匹配 设置相似性阈值。改值越接近 1，查找值与源值重复可能性越高 相似性输出别名 为包含所选联接相似性得分的新输出列指定名称.如果将该 值保留空,将不会创建输出列. 数字 指定比较列数据时前导数字和尾随数字的重要性.Neither前导数字和尾随数字都不重要。 Leading-只有前导数字重要。 Trailing-只有尾随数字重要。LeadingAndTrailing-前导和 尾随数字都重要 比较标志 包含忽略大小写、忽略假名类型、忽略符号、忽略字符宽度 等等。3 高级选项卡在高级选项卡中可以指定输入列和输出列、设置相似性阈值和定义分隔符。见下 图了解这些信息后， 我们现在就已例子来介绍如何使用模糊分组转换功能。假设人 事部在输入每个员工的时候会有一些笔误。 这里我们使用模糊分组来得到最标准 的职位名称和每个员工的职位和这个标准的名称的相似度。 1.在 E:\BI 资料\IntegrationServices 文件下创建一个名为“EmployData”的 文档。文档内容如下:2.创建一个名为”FuzzyGroupDemo”的包，在控制流界面拖放一个 DataFlow 任 务。 然后在联接管理器界面创建一个名为“EmployData”的平面文件联接器，连 接上面创建的 txt 文件。 3.在常规选项卡中设置格式属性为右边未对齐(Ragged Right)，代码页属性设置 为 1252（ANSI-拉丁语 I）。其他设置参考如下图:4. 在列选项卡中点击列边界分别在第 5 个字符和第 28 个字符处点击放置两个分 割线如图。5. 点击高级标签设置 TITLE 的 OutputColumnWidth 为 50，保存连接。6. 在数据流选项卡中拖放一个平面文件数据源。平面文件连接器属性设置为 EmployData。 7. 拖放一个模糊分组转换任务。并将于平面文件源连接，箭头指向模糊转换任 务。 8. 在模糊分组转换任务中的连接管理器属性中单击新建按钮。创建一个 OLE DB 连接管理器。用于连接 AdventureWorks 数据库。 9. 在列属性中，选择 Title 列做为输入列，其他默认输入。如图:10. 在高级选项中，相似性阈值设置为 0.5，其他都是默认. 11. 拖放一个 OLE DB 目标源，设置 OLE DB 连接管理器为刚才创建的数据库连接 器。数据访问模式设置为表或试图-快速加载、单击新建，弹出对话框对框中的 sql 脚本做如下设置: CREATE TABLE [FuzzyGroup] ( [key_in] int primary key, [key_out] int, [score] real, [EMPID] varchar(5), [TITLE] varchar(50), [LNAME] varchar(50), [TITLE _clean] varchar(50), [_Similarity_TITLE] real ) 然后单击确定。 12. 单击映射选项卡。做如下设置。13 执行包。成功后如下图：13. 查看结果， AdventrureWorks 数据库中的表 FuzzyGroup 中查看结果如下: 在OK,模糊分组转换功能介绍完毕。SSIS 组件转换_排序、合并、合并联转换一、排序转换（Sort）排序转换功能是按升序或降序对输入流中的数据进行排序.并将排序后的数据复制到转换输 出中.它类似于 T-SQL 中的 order by 语句。下面以数据库 AdventureWorks 为例来介绍一下 排序转换的使用。 1.新建名为”SortDemo”的包， 在控制流中拖放一个数据流任务， 然后在连接管理器中建立 一个 OLE DB 连接器，用于连接 AdventureWorks 数据库。 2.在 BIDS 界面左边的变量工具箱中设置一个变量 Count 类型是 Int32。 3.在数据流选项卡中拖放一个 OLE DB 数据源，连接 AdventureWorks，选择 Production.product.如图:4.拖放一个排序转换任务。按如下图设置:下面详细介绍一下该图中各个属性的使用方法: 属性 可用输入列 名称 传递 输入列 输出别名 排序类型 排序顺序 说明 使用此复选框可以指定要排序的列 可用输入列的名称 指示是否在排序输出中包含该列 选择需要排序的列，也可以通过可用输入列中选择 为要排序的输入列设置一个输出别名，默认为输入列名称 指示是按升序还是按降序排序 指示列的排序顺序。必须是手动设置此项，而且设置时按初始值为 1 的递增 数据上图中理解为先按 Name 降序，再按 ProductName 升序排序。 5.拖放一个行计数转换，将排序与之连接，箭头指向行计数转换。并设置其中变量 User::Count 在变量工具箱中创建。6.在排序转换与行计数转换中的绿色连线上建立一个带有 Grid 网格的查看器。 然后执行包。 如下图:我们发现 Name 和 ProductName 列按我们在排序转换中的设置输出到数据管道中。二、合并转换(Merge)合并转换是将两个排序后的数据集合合并为一个数据集。 根据每个数据集中的行的键列的值， 将这些行插入到输出中。合并转换功能类似于 T-sql 语句中的 Union All 子句。合并转换要求输入列具有匹配的源数据。在 SSIS 设计器中，合并转换的用户界面会自动映 射映射具有元数据的列。然后你可以手动映射具有兼容数据类型的其他列。 下面我们以 AdventureWorks 数据库中的 Production.Product 表为例介绍如何使用合并转换。1.创建一个名为“MergerDemo”的包，然后在控制流中拖放一个 DataFlow 任务，在连接管 理器中创建一个 OLE DB 连接器，用于连接 AdventureWorks 数据库。 2.在 BIDS 中的变量工具箱中创建一个变量 Count，类型为 Int32。 3.在数据流中拖放两个 OLE DB 数据源。连接器连接 AdventureWorks 数据库,两个数据源的 访问模式都选择 sql 命令。Sql 语句分别为: select top 5 * from Production.Product select top 6 * from Production.Product 4.在数据流中拖放两个排序转换功能。 分别对两个数据源数据的列 ProductNumber 按升序排 序。 5.拖放一个合并转换，分别与两个排序转换连接。设置如下：6.拖放一个行计数。与合并转换相连。设置 VariableName=User：：Count 7.在合并转换与行计数转换中间的连接创建一个数据查看器。然后执行包。结果如下:8.在弹出框中可以看到我们合并的部分数据。 可以持续单击绿色三角按钮来查看合并的数据。 想让包执行完毕。可以关闭弹出框即可。 到此， 合并转换功能的使用已介绍完毕。 其中一些注意的事项我们将与合并联接转换一起介 绍。三、合并联接转换(Merge Join)合并联接转换功能是将两个已排序的输入列的数据通过使用 FULL JOIN、 Left JOIN 或 INNERR JOIN 联接形成一个输出数据集。它类似于 T-SQL 语句中的连接查询语句。但又与之有所区 别。?使用合并联接要求输入已排序的数据。而 T-SQL 中的连接查询不需要?如果两个输入列关联的列是字符类型。则需要对它进行大小写转换或去除空格等操 作。而在 T-sql 的连接查询中不需要。了解这些信息后，下面介绍如何使用。 1.创建一个名为“MergeJoinDemo”包，在控制流中拖放一个 Data Flow 任务。在连接管理 器中添加一个 OLE DB 连接器。用于连接 AdventureWorks 数据库。 2.在变量工具箱中创建一个如下图的变量3.在数据流选项卡中拖放两个 OLE DB 数据源。都连接刚才创建的名为 “LocalHost.AdventureWorks”连接器。数据访问模式使用 sql 命令。Sql 语句分别如下 select ProductID,ProductModelID,Name from Production.Product select ProductModelID,Name from Production.ProductModel 4.拖放两个排序转换到数据流选项卡中，分别对两个数据源数据的列 ProductModelID 按升 序排序。 5.拖放一个合并联接转换，分别与两个排序转换连接，然后双击合并联接转换，弹出窗口根 据窗口设置合并联转换属性。在图中有一个联接类型选项，它包含三种类型:左外部联接、内部联接和完全外部联接，分 别等同于 T―SQL 中的 LEFT JOIN、INNER JOIN 和 FULL JOIN 6.拖放一个行计数。与合并联接转换相连。设置 VariableName=User：：Count 7.在合并转换与行计数转换中间的连接创建一个数据查看器。然后执行包。结果如下:在弹出框中可以看到我们合并的部分数据。可以持续单击绿色三角按钮来查看合并的数据, 想让包执行完毕,可以关闭弹出框即可SSIS 组件转换_查找转换查找转换功能和模糊查找转换功能类似。 只不过查找转换相比模糊转换容易的多。 相当于模糊查找转换中的完全匹配设置，查找转换包含以下属性?常规选项在常规选项卡中我们可以看到如图的一些设置:图中的具体含义如下:属性 说明完全缓存 部分缓存在执行查找转换前，生成引用数据集并将其加载到缓存中。 在执行查找转换的过程中生成引用数据集。将在引用数据集内有 匹配项的行加载到缓存中，并将数据集内没有匹配项的行加载到 缓存中。 在执行查找转换的过程中生成引用数据集。不向缓存中加载任何 数据。 将查找转换功能配置为使用缓存连接管理器。只有当选择了“完 全缓存”选项时，此选项才可用。 将查找转换功能配置为使用 OLE DB 连接管理器。无缓存 缓存连接管理器 OLEDB 连接管理器指定如何处理无匹配 选择一个选项来处理在引用数据集内没有任何匹配项的行。 项的行 如果选中“将行重定向到无匹配输出”，则行将重定向到无匹配 输出， 并且将不作为错误处理。 “查找转换编辑器”对话框的“错 误输出”页上的“错误”选项不可用。 如果选中“指定如何处理无匹配项的行”列表框中的任何其他选 项，则行将作为错误处理。“错误输出”页上的“错误”选项不 可用。n 连接选项 如果在常规页中， 如果连接类型选择缓存连接管理器。 则在连接页中的格式如下:否则连接管理器的格式如下:然后我们在连接页中根据需求设置这些属性。 n 列选项n 高级选项n 错误输出选项了解查找转换各个属性页以后，现在我们简单的介绍一下使用方法。1. 创建一个名为“LookupDemo”的包，在控制流中拖放一个 Data Flow Task，在连接管理 器中创建一个 OLE DB，连接数据库 AdventureWorks。 2. 在数据流选项卡中拖放一个 OLE DB 数据源。做如下设置:1. 拖放一个查找转换，与 OLE DB 关联，箭头指向查找转换。然后根据以下图片进行设 置设置关联是用鼠标在可用输入列中选中 ProductModelID，然后托动到可用查找 列的 ProductModelID，这样红色中的一条线就建立成功。自此，将可用输入列 与可用查找列建立起来了关联关系。其他的属性页设置默认即可。1. 在变量工具箱中创建一个 Count 变量，如图:1. 托动两个行计数转换，按如下设置；其中设置两个行计数转换的属性 variableName 为@Count1. 在查找与行计数转换连接线上右击，选中数据查看器，然后添加一个网格查看器。 如下图1. 执行包， 弹出查看器， 在这查看器中， 我们可以浏览存储在数据管道中的数据信息。 最终执行的结果如下:4.2.13 排序转换(Sort)SSIS 组件转换_模糊查找转换 模糊查找转换与查找转换之间的不同之处在于:它使用了模糊匹配。查找 转换使用同等连接在引用表中查找匹配记录。它返回引用表中完全匹配的项。或 者不返回任何内容。 与此相比， 模糊查找转换使用模糊匹配返回引用表中一个或 多个接近的匹配项。 模糊查找转换的引用数据源必须是 sql2000 或更高版本的数据库。 而且在 模糊匹配中， 只能使用具有 DT_WSTR 和 DT_STR 数据类型做为输入列。完 全匹配 可以适用除 DT_TEXT、 DT_NTEXT 和 DT_IMAGE 之外的所有数据类型,模糊查找转换 的编辑框中有三个选项卡。我们现在就逐一介绍1.引用表选项卡属性 OLEDB 连接管理器 说明 使用列表框选择现有的 OLE DB 连接管理器，或使用“新建”按钮创建新的 连接 生成新索引 引用表的名称 存储新索引 新索引名称 维护存储的索引 使用现有索引 现有索引名称 指定转换时应创建新的索引以用于查找 在数据库中选择要作为引用的表 如果希望保存新的查找索引，请选择此项 如果已选择存储新索引选项，则可以对此项设置名称 如果已选择存储新索引选项，则可以对此项进行选择 指定转换时使用现有索引来执行查找 从数据库中选择要做为引用的表2. 列选项卡属性 可用输入列 说明 拖动输入列以将其连接到可用查找列。 这些列必须具有所支持的相互匹配的数据类型。 选择一个映射行，再右键单击可在创建关系对话框中编辑该映射 名称 传递 可用查找列 查找列 查看可用输列的名称 指定是否在转换的输出中包含输入列 使用这些复选框可以选择要对其执行模糊查找操作的列。 从引用表的可用列的列表中选择查找列。通过选中“可用查找列”表中的相应复选框 即可选择查找列。选择“可用查找列”表中的某个列将创建一个输出列，其中包含返 回的对应于每个匹配行的引用表列值。 输出别名 为每个查找列的输出键入一个别名。默认值为查找列的名称，并追加一个数字索引值； 不过，您也可以任选一个唯一的描述性名称。3. 高级选选卡属性 每次查找输出的最大匹配数 说明 指定为每个输入行返回的最大匹配转换数。默认值为 1.如果设置 大于 1 的值，则对于每次查找，转换的输出可能包括多行，而且 其中一些行可能是重复的。 相似性阈值 使用滑块在组件级别设置相似性阈值。 该值越接近 1， 查找值与源 值的相似性必须越接近， 才能视为匹配。 由于需要考虑的候选记录 更少，因此增加阈值可以提高匹配的速度。 标记分割符 指定转换用来对列值进行词汇切分的分隔符。接下来我们将一个例子介绍模糊查找转换的使用方式。 我们利用上一章节中提供的 txt 文本 做为数据源。 1.在 AdventureWorks 数据库中使用下面 T-SQL 语句创建一张表2. 创建一个名为“FuzzyLookupDemo”的包，然后拖放一个 Data Flow Task 到 控制流中。在连接管理器界面创建一个名为“EmployData”的平面文件联接器， 连接” E:\BI 资料\IntegrationServices\EmpolyData.txt” 3.在常规选项卡中设置格式属性为右边未对齐(Ragged Right)，代码页属性设置 为 1252（ANSI-拉丁语 I）。其他设置参考如下图:4.在列选项卡中点击列边界分别在第 5 个字符和第 28 个字符处点击放置两个分 割线如图。5.点击高级标签设置 TITLE 的 OutputColumnWidth 为 50，保存连接。6.在数据流选项卡中拖放一个平面文件数据源。平面文件连接器属性设置为 EmployData。 7.拖放一个模糊查找转换任务。 并将于平面文件源连接， 箭头指向模糊转换任务。 8.在模糊查找转换任务中的引用选项卡中连接管理器属性中单击新建按钮。 创建 一个 OLE DB 连接管理器。用于连接 AdventureWorks 数据库。其他的设置参考如 下图9.在高级选项中，相似性阈值设置为 0.5，其他都是默认。如下图设置10.列选项卡和高级选项卡中的属性设置依次参考下面的两个图：
11.拖放一个 OLE DB 目标源，设置 OLE DB 连接管理器为刚才创建的数据库连接 器。数据访问模式设置为表或试图-快速加载、单击新建，弹出对话框对框中的 sql 脚本做如下设置:然后单击确定。 12.单击映射选项卡做如下设置。13.执行包。成功后如下图：14.可以在 AdventureWorks 数据库中查看结果:
SSIS 包配置SQL ServerIntegration Services 提供可用于在运行时更新属性值的包配置。 配置是添加到已完成包中的属性/值对。通常，在包开发期间您在包对象上创建 包设置属性，然后将配置添加到包中。当 包运行时，它从配置中获取新的属性 值。例如，通过使用配置，您可以更改连接管理器的连接字符串，或者更新变量 的值。 包配置具有下列优点：? ??使用配置可以更轻松地将包从开发环境转移到生产环境中。例如，配置可以更新源 文件的路径，或者更改数据库或服务器的名称。 将包部署到多台不同的服务器时，配置非常有用。例如，用于每个已部署包的配置 中的变量可以包含不同的磁盘空间，并且如果可用磁盘空间不满足此值，包将不会 运行。 配置可以使包更加灵活。例如，配置可以更新在属性表达式中使用的变量的值。Integration Services 支持几种不同的存储包配置 （例如 XML 文件、 Server SQL 数据库中的表以及环境变量和包变量）的方法。 每个配置都是一个属性/值对。XML 配置文件和 SQL Server 配置类型可以包括 多个配置。一 xml 配置文件选择 SSIS 菜单―&包配置，打开如下窗口，包配置就从这里开始配置。选择启用包配置复选，然后单击添加按钮，如图:1. [配置类型]下拉列表框中可以看出，有多种类型的包配置。我们在这里选择 “XML 配置文件” 2.选择直接指定配置设置 3.单击浏览将配置文件存放到指定的路径。 配置文件默认为”.dtsconfig”类型，其文件格式时标准的 XML 格式。 Integration Services 对配置文件的扩展名没有严格的规定。 如果把扩展名改 为.txt、.xml 等都没有关系。单击[下一步]按钮，选择配置需要的属性了。如 图:在左边的对话框中， 我们可以根据需要勾选要配置的属性，当选中一个属性的时 候，该属性的默认值就会在右边属性特性框中，这个值是可以改变的。完成属性 更改后，单击下一步按钮，如图:输入[配置名称]，点击完成，XML 的配置就这样轻松地完成了。在包配置组织程 序框中将会出现如下图显示。然后单击关闭按钮,整个包配置的过程就结束。我们转到配置文件存放的目录，找到刚才的配置文件 &SqlConnectionString.dtsconfig &，看看它象什么样子： &?xml version=&1.0&?& &DTSConfiguration& &DTSConfigurationHeading& &DTSConfigurationFileInfo GeneratedBy=&******& GeneratedFromPackageName=&ReadRecommendation& GeneratedFromPackageID=&{47D1A86F-A1E0--323C027DE88F}& GeneratedDate=& 10:00:21&/& &/DTSConfigurationHeading&&Configuration ConfiguredType=&Property& Path=&\Package.Connections[RecommendationDB].Properties[ConnectionStr ing]& ValueType=&String&& &ConfiguredValue&Data Source=******;User ID=****;Initial Catalog=RecommendationDB;Provider=SQLNCLI10.1;Auto Translate=FApplication Name=SSIS-ReadR&/ConfiguredValue& &/Configuration& &Configuration ConfiguredType=&Property& Path=&\Package.Connections[RecommendationDB].Properties[Password]& ValueType=&String&& &ConfiguredValue&******&/ConfiguredValue& &/Configuration& &/DTSConfiguration& 这个配置文件中， 包含数据库的连接方式，如果需要修改数据库连接只要更 改相应的属性就可以搞定了。二、SQL Service 配置这也是我们常用的配置方式之一，配置方式与 XML 配置大同小异，只是最后的 配置属性存储到 SQL Service 数据库中。在[配置类型]下拉列表框中，选择[SQL Server ]，下面的配置界面也随着发生 了变化。 [连接]，即 SQL Server 即数据库的连接，如果从下拉列表框中，找不到对应的 连接，点击[新增]，就可以创建一个新的连接，这与在连接管理器中，创建 OLE DB 连接是一样的。 [配置表(A)]，即配置属性存放的 Table ，如果这个 Table 没有事先创建好，点 击[新建]， 系统会默认给出其如下 SQL 语句， 点击[确定],Table 就创建完成了。 CREATE TABLE [dbo].[SSIS Configurations] ( ConfigurationFilter NVARCHAR(255) NOT NULL, ConfiguredValue NVARCHAR(255) NULL, PackagePath NVARCHAR(255) NOT NULL, ConfiguredValueType NVARCHAR(20) NOT NULL )[配置筛选器(F)]：这个很关键，它是区分每个包的配置属性的 key 值，也就是 说，如果一个 Table 中，存放有多个包的配置属性的时候，每一个包就是根据 这个这个 key 值来进行过滤的， 所以不同的包， 应该有不同的 key 值。 [下一步]， 选择要配置的属性，与上面讲的[XML 配置文件]的处理方式一致了。下面，我们 回到 SQL Server 中，找出刚才创建的记录，看看它又是啥面貌：三、父包变量父包变量的定义也非常简单，打开包配置向导，如下图：[配置类型]：选择“父包变量”。[父变量]：这里输入父包中的变量的名称。 [下一步]，选择父包变量映射的属性名称，最后点击完成，就会获得如下图所示 的效果。我们常见的包配置就这些了，环境变量、注册表这两种类型很少用到，这里就 不多讲了。 包配置给包的部署带来了非常大的方便， 最好的方式是每个服务器使用一个 配置文件。但是，从包的配置界面可以看出，一 个包可以应用多个配置文件， 每个包配置按照[包配置组织程序]中显示的顺序应用（父包变量除外），如果一 个配置中有一项失败了，它只会发出警告，也不会引 起包错误；反之，一个配 置文件也可以应用到多个包中，如果我们灵活应用，包的无缝移置，也就不再是 神话了。
赞助商链接
SQLServer 的 SSIS 的核心功能介绍 最近在用 SQL SERVER 的 ssis 工具导入另外一个系统的数据,一直没有静下来系统的总 结一些其中的学习及研究收获,恰好找到一篇...新建作业:打开 SQL SERVER Management Studio 展开代理,在“作业”上右击新建...ssis实例 20页 1下载券
SSIS 学习(8)事务 6页 5下载券
1 SSIS基本结构...SSIS实例学习_IT/计算机_专业资料。SSIS实例,数据抽取,ETLSQL SERVER SSIS 实例 数据的导出涉及到数据转换的概念。企业中的数据并不是以统一的格式存储的。比如: ...SQL Server 2008 Analysis Services 入门教程_计算机软件及应用_IT/计算机_专业资料。ssis学习资料 【数据挖掘】SQL Server 2008 Analysis Services 入门教程 2012-11...接着,新建一个 ImportDimGamePackage.dtsx 的 SSIS 包。 拖放一个数据流任务...一步一步学习 sqlserverBI--多维数据库建立 多维数据库建立接着我们上节的《...2005 自带的例子 数据库,它们在 SQL Server 2005 中取代了大家在 SQL Server 2000 中熟悉 的 Northwind 数据库,是练习学习 SSAS, SSIS 和 SSRS 的最好的工具...SSIS 学习(1) 概要 俗话说:“十年磨一剑”,Microsoft 通过 5 年时间的精心打造,于 2005 年浓重推出 Sql Server 2005,这是自 SQL Server 2000 以后的又一...详解SQL Server中SSIS事件探查器的使用_IT/计算机_专业资料。详解 SQL Server 中...SQL Server SSIS_2008_集... 61页 4下载券 SQL Server SSIS 学习指... 13...(SQL Server Agent),在目录”Proxies/SSIS Package Explore” 下新增了一个可以...SSIS操作指南 10页 1下载券 SSIS 学习(7):包部署 12页 免费
DATASTAGE JOB...SSIS包的部署、配置、定期执行_IT/计算机_专业资料。SSIS自动执行包...图9 D:\Program Files2\Microsoft SQL Server\100\DTS\Packages\学习项目 图 ...
All rights reserved Powered by
www.tceic.com
copyright &copyright 。文档资料库内容来自网络，如有侵犯请联系客服。