部署脚本 SSIS SQL

逐步学习 Microsoft 商业智能第 3 天

Shivprasad koirala

4.87/5 (33投票s)

2014 年 6 月 25 日

CPOL

21分钟阅读

147882

385

这是逐步学习 MSBI 系列的第 3 部分

此 MSBI 学习文章已过时，我们建议查看我们使用 SQL Server 2014 的最新文章：https://codeproject.org.cn/Articles/1064477/Learn-to-create-MSBI-Microsoft-Business-Intelligen

引言

欢迎来到逐步学习 Microsoft 商业智能的第 3 天。在本文中，我们将深入探讨 SSIS 世界。我们将揭示 SSIS 中的一些强大功能和任务。

除了 MSBI 逐步文章和视频，请观看我们的实用 MSBI 面试问答教程。

完整系列

第 1 天 - 理解数据仓库、商业智能、SSIS、SSAS 和 SSRS 基础知识、使用 SSIS 进行简单 ETL
第 2 天 - 维度和事实、数据仓库设计技术、SSIS - 执行 SQL 任务、SSIS – 备份数据库任务、SSIS – 脚本任务和变量。
第 3 天

理解优先约束

在我们开始实验之前，首先让我们谈谈“优先约束”。

优先约束让我们决定任务的执行顺序。例如，哪个任务应该首先执行，哪个任务其次执行，依此类推。
它还让我们决定任务执行的条件。

例如，

如果上一个任务失败则执行
如果上一个任务成功则执行。
在上一个任务完成时执行（无论成功或失败）
当某些条件匹配时执行。

仍然感到困惑！！！

别担心。在接下来的实验中，我们将使用优先约束，之后所有的困惑都将烟消云散。

控制流

到目前为止，在控制流中，我们已经了解了“数据流任务”、“执行 SQL 任务”、“备份数据库任务”和“脚本任务”。让我们进一步探索。

实验 5 - Web 服务任务

“Web 服务任务”允许我们执行 Web 服务方法，然后将结果存储在变量或文件中。

此变量或文件可作为其他任务的输入。

注意

本文的目的是解释 SSIS，而不是 Web 服务。我们假设您对 Web 服务有基本了解。如果您是 Web 服务新手，那么在步骤 1 和步骤 2 中，我们分享了两个视频，它们将让您熟悉 Web 服务。
在实际场景中，作为 BI 开发人员，通常不需要担心 Web 服务的创建。您将从外部源（可能来自其他开发人员或组织）获得 WSDL 形式的服务元数据。您的任务将是添加“Web 服务任务”，这就是我们将要学习的。

步骤 1：创建演示 WCF 服务

请观看 www.Questpond.com 的以下视频，了解如何创建简单的 WCF 演示服务。

步骤 2：在 IIS 或控制台应用程序中托管演示服务

请阅读 www.Questpond.com 的以下文章，了解如何托管 WCF 演示服务。

步骤 3：创建新的集成服务项目

打开 SQL Server 数据工具（或商业智能开发工作室）。点击“文件”>>“新建”>>“项目”。从创建项目对话框的左侧选择“集成服务”，输入项目名称为“WebServiceDemo”，然后点击“确定”。

步骤 4：创建 HttpConnection Manager

右键单击底部的连接管理器，然后选择“新建连接”。
从对话框中选择 HTTP，然后单击“确定”。
在下一个对话框中输入 WSDL 文件的地址。
注意：如何获取 WSDL 地址不是您的关注点。创建服务的人员会提供给您。

注意：如果服务接受，请提供用户名和密码或证书。此信息也将提供给您。
点击“确定”。

步骤 5：添加 Web 服务任务并进行配置

从 SSIS 工具箱中取出 WebService 任务并将其添加到 SSIS 设计器选项卡。

双击该任务。它将打开“Web 服务任务编辑器对话框”。
将 HttpConnection 属性设置为上一步中设置的值。
将 WSDL File 属性设置为某个绝对文件路径。
注意：不要选择路径，而是键入它，因为 WSDL 文件尚不可用。

路径应为 “Some_Valid_Folder_Path\AnyFileName.wsdl”

例如，将其设置为“G:\BI Step by Step\3\Source Code\WebServiceTask\Service.wsdl”
在底部您会找到一个“下载 WSDL”按钮。点击它。它会将服务公开的 wsdl 下载到上一步中指定的路径。

现在将左侧的选择从“General”更改为“Input”。
将服务和方法设置为下拉列表中的值之一。当我们点击上一步中的“下载元数据”时，这两个下拉列表都已添加了值。

导航到输出部分，并将输出类型设置为变量。接下来将变量设置为“新建变量”。输入变量名为“ServiceOutput”，并将类型设置为 String。单击“确定”关闭“添加变量”窗口。单击“确定”关闭“Web 服务任务编辑器”。

步骤 6：创建脚本任务

从 SSIS 工具箱中添加脚本任务并将其配置为在消息框中显示“ServiceOutput”变量的值。请参阅第 2 天了解如何使用脚本任务。

步骤 7：决定执行流程。

使用优先约束，使 Web 服务任务首先执行，然后执行脚本任务。

点击 Web 服务任务。您会看到一根绿色的箭头从中引出。
拖动该线并将其连接到 ScriptTask。

注意：如果我们不在这里使用优先约束，这两个任务将并行执行。使用优先约束，我们添加了一个约束：“一个任务只有在另一个任务成功执行后才能执行”。

步骤 8：执行和测试

确保服务正在执行。（对于演示，您可以使用文章中附加的 WCF 服务。从 WCF 服务项目执行托管项目。）
按 F5 并确认包正在工作

实验 6 - XML 任务

如您所见，我们正在从 Web 服务获取 XML。

为了从中提取值，我们将使用“xml 任务”。

“Xml 任务”让我们处理 xml 数据。使用它，

我们可以合并多个 xml 文档，
我们可以将 XLST 样式表应用于 xml 文档
我们可以使用 XPath 表达式评估值
我们可以验证 XML 文档
我们可以比较 Xml 文档

步骤 1：准备项目

使用上一个实验（Web 服务实验）中创建的相同项目，并从中移除“脚本任务”。

步骤 2：添加 XML 任务并进行配置

从 SSIS 工具箱中取出“Xml 任务”并将其添加到控制流中。

步骤 3：添加变量以存储最终结果

右键单击设计器部分，选择变量并添加一个名为“XmlOutput”的变量。

步骤 4：配置 XML 任务

双击“xml 任务”，它将打开“XML 任务编辑器窗口”。
按如下设置属性
1. I. 源类型设置为变量
2. II. 源设置为 User::ServiceOuput（此变量将包含 xml 格式的 Web 服务返回值，它是在上次演示中创建的）
3. III. 操作类型设置为 XPATH
4. IV. XPath 操作设置为“值”
5. V. 第二次操作类型设置为 DirectInput
6. VI. 第二个操作数设置为“/string”
  注意：如果需要，您可以将 XPath 表达式存储在文件或变量中并直接使用。为此，请相应地将 SecondOperationType 设置为“文件连接”或“变量”。
7. VII. SaveOperationResult 设置为 True
8. VIII. DestinationType 设置为 variable
9. IX. Destination 设置为 XmlOutput（它是在上一步中创建的）
10. X. OverwriteDestination 设置为 True
单击“确定”

步骤 5：添加和配置脚本任务

在控制流中添加脚本任务，并配置它以在消息框中显示 XmlOutput 变量的值。

步骤 6：决定执行流程

使用优先约束，使“Web 服务任务”首先执行，然后是“Xml 任务”，最后是“脚本任务”。

步骤 7：执行并测试应用程序

按 F5 并检查输出

实验 7：使用事件处理程序在控制流中进行错误处理

错误处理一直是每种技术中都引人入胜的功能。SSIS 怎么会落后呢？J

让我们以上面的实验中创建的相同示例进行此演示。但这次服务将不可用，因此 Web 服务任务将无法正常执行并会抛出错误。

步骤 1：准备项目

I. 取用上述实验 6 中创建的示例。

II. 停止 Web 服务（如果它正在执行）

步骤 2：创建事件处理程序

I. 在 SSIS 设计器窗口中点击事件处理程序选项卡。

II. 您会在顶部找到两个下拉菜单，名为“可执行文件”和“事件处理程序”。选择“Web 服务任务”作为可执行文件，选择“OnError”作为事件处理程序。

III. 在设计器窗口的工作区域，您会找到一个链接“点击此处为可执行文件‘Web 服务任务’创建一个‘OnError’事件处理程序”。点击它。

IV. 就是这样。它将创建事件处理程序。

步骤 3：添加脚本任务并进行配置

现在在事件处理程序的工作区域添加一个脚本任务。
双击任务以打开“脚本任务编辑器”。
确保为 ReadOnlyVariable 属性选择一个名为“Error description”的系统变量。

注意

“Error Description”是一个系统变量，它将包含 SSIS 包中的最新错误描述。
每次发生新错误时，变量都会更新。
我们可以将此变量用作其他任务的输入。

点击“编辑脚本”并编写代码以在消息框中显示“错误描述”变量的值。

MessageBox.Show(Dts.Variables["ErrorDescription"].Value.ToString());

步骤 4：执行并测试应用程序

按 F5 并确认一切正常。

实验 8 – 使用优先约束在控制流中进行错误处理

[正在进行中 – 将很快更新]

容器

SSIS 中的容器允许我们将多个任务分组到一起。通过将多个任务添加到同一组中，我们获得了以下好处。

轻松控制执行顺序。

我们举一个例子。

假设我们有一个任务 1，我们希望任务 2 在任务 1 之后执行。挑战在于，一旦任务 2 完成，任务 3、任务 4 和任务 5 应该并行执行，最后，一旦所有这些任务都完成，任务 6 应该开始。如果没有容器，那将是一场噩梦，因为在实时场景中，我们可能有大量的任务，并且仅凭优先约束来控制所有这些任务将很困难。

一次性为多个任务设置公共属性值。例如，一键禁用多个任务。
我们可以为容器添加事件处理程序，而不是为每个任务单独添加事件处理程序。在上次演示中，我们为单个任务添加了“OnError”事件处理程序。如果我们有许多任务怎么办？只需将所有任务放入一个容器中，并为容器添加“OnError”事件处理程序。
到目前为止，我们创建的所有变量都是包级变量。这些变量的生命周期等于包的生命周期。使用容器，我们可以将变量的范围限制在容器中。
我们可以在事务中执行多个任务。如果一个任务失败，其他任务执行的操作将回滚。

容器类型

序列容器 – 只是将任务分组到一起
For 循环容器 – 将任务分组并让它们执行“n”次
For Each 循环容器 – 将任务分组，让我们遍历项目集合（如文件），并让我们在该循环中执行“任务组”。（循环中的每个项目都可以作为容器内一个或多个任务的输入）

实验 9 - 序列容器

在这个实验中

我们将多个任务分组到一个任务中。
然后我们将使它们在事务中执行。

步骤 1：准备数据库

在 SQL Server 中创建名为 ContainerDemo 的数据库和如下所示的 Customer 表

步骤 2：创建集成服务项目

使用 SQL Server 数据工具创建新的 SSIS 项目。

步骤 3：添加连接管理器

添加 Ado.net 连接管理器并将其连接到 ContainerDemo 数据库

步骤 4：添加容器

在控制流中添加序列容器。

步骤 5：添加执行 SQL 任务

在序列容器中创建三个“执行 SQL 任务”。
将它们三个的 ConnectionType 都设置为 ADO.NET。
将它们三个的连接都设置为步骤 3 中创建的连接。
将它们三个的 SQLSourceType 都设置为 DirectInput。
将 SQLStatement 设置为
1. 第一个任务为 insert into Customer values('A',55)
2. 第二个任务为 insert into Customer values('B',60)
3. 第三个任务为 insert into Customer values('C','CC')

注意

第三个查询将导致错误，因为 Age 是整数，而我们传递的是字符串。
不要使用优先约束连接序列容器中的三个任务。让它们并行执行。

步骤 6：执行和测试

按 F5 并测试应用程序。

步骤 7：启用事务

右键单击序列容器并选择属性
在属性窗口中，将 TransactionOption 属性的值设置为“Required”

步骤 8：执行和测试

按 F5 并测试应用程序

注意：确保在执行之前删除表中所有现有记录，以便您清楚地了解记录插入过程而没有任何混淆。

实验 10 - For 循环容器

在本实验中，我们将学习如何将变量的范围限制在容器中以及如何执行循环。

步骤 1：准备数据库

我们将使用上面实验中创建的相同数据库。确保清空 Customer 表，以免产生任何混淆。

步骤 2：创建新的集成服务项目

使用 SQL Server 数据工具创建新的 SSIS 项目。

步骤 3：添加连接管理器

添加 Ado.net 连接管理器并将其连接到 ContainerDemo 数据库

步骤 4：添加容器

在控制流中添加“For Loop 容器”。

步骤 5：创建变量

为了循环，我们首先需要变量。在包级别创建一个名为 Counter 的变量。

步骤 6：添加序列容器

在同一个控制流中再添加一个序列容器。

步骤 7：检查当前变量范围。

确保变量窗口已打开。

点击序列容器。您会在变量窗口中找到 Counter 变量。
点击 for 循环容器。Counter 变量仍然可用。

步骤 8：更改变量范围

在变量窗口中选择变量，点击“移动变量”按钮。
将弹出一个名为“选择新范围”的新窗口。选择前面步骤中添加的 for 循环容器。

步骤 9：重新检查变量范围。

再次执行步骤 7。

步骤 9：移除序列容器

选择序列容器并按删除键。该容器在此演示中的唯一目的是测试。

步骤 10：配置 For 循环容器。

双击“For 循环容器”。它将打开“For 循环编辑器”窗口。
按照图中所示设置属性值。

注意：现在不言自明。For 循环将执行五次。

步骤 11：添加执行 SQL 任务并配置它。

在 For 循环容器中添加一个新的执行 SQL 任务。
按如下配置其属性。
1. ConnectionType 为 ADO.NET。
2. 连接到上面某个步骤中创建的连接。
3. SQLSourceType 为“DirectInput”
4. 将 SQLStatement 设置为“insert into Customer values('A'+cast(@Index as varchar),@Index)”
在“执行 SQL 任务编辑器”窗口中，转到“参数映射”部分，并将 SQL 参数 @index 映射到 SSIS 参数 @Counter。
点击“确定”

步骤 12：执行和测试

按 F5 执行包并测试输出

实验 11 - For Each 循环容器

在本实验中，我们将遍历位于目录中的 CSV 文件，并将其所有数据存储到 SQL Server 中。

步骤 1：准备数据库

将使用相同的 ContainerDemo 数据库和 Customer 表。请务必截断它。

步骤 2：创建 SSIS 项目

使用 SQL Server 数据工具创建新的 SSIS 项目

步骤 3：创建数据文件夹和数据文件

创建 3 个包含一些数据的 CSV 文件，并将它们放在某个文件夹中。

步骤 4：创建变量

创建一个名为“FilePath”的新变量

步骤 5：添加并配置“For Each 循环容器”

将 For Each 循环容器添加到控制流中
双击容器以打开“Foreach 循环编辑器”
将左侧的选择从“General”更改为“Collection”。
将枚举器设置为“ForEach File Enumerator”
选择文件夹路径
在“Files”文本框中输入“*.csv”
将选择从“Collection”更改为“Variable mapping”。
将变量设置为上一步中创建的变量，将 Index 设置为 0

点击“确定”。

步骤 6：创建数据流任务

在数据传输方面，数据流任务是唯一的选择。从工具箱中取出“数据流任务”并将其添加到容器中。

步骤 7：添加连接管理器

添加 Ado.net 连接管理器并将其配置为指向 ContainerDemo 数据库
添加平面文件连接管理器并明确将其指向目录（数据目录）中的一个文件

步骤 7：配置数据流任务中的源和目标。

双击“数据流任务”。它将带您进入数据流选项卡。
添加平面文件源，双击它以打开平面文件源编辑器。

将连接管理器设置为上一步中创建的“平面文件连接”。

添加 Ado.Net 目标
1. 使用“数据流路径”将平面文件源连接到此目标
2. 双击 Ado.Net 目标以打开“Ado.Net 目标编辑器”
3. 将连接管理器设置为上一步中创建的“Ado.Net 连接”。
4. 选择表为 Customer。
5. 导航到映射部分，确保映射正确。

注意：您可能在想“我们将在文件上执行循环，那么为什么我们将源配置为单个文件”。答案是：放松！！J 我们还没有完成。我们将源连接到其中一个文件，因为这样做我们了解了源文件结构，并且可以进行映射。

步骤 7：使连接动态化

右键单击平面文件连接并转到属性
找到表达式属性并单击 3 点按钮。它将弹出“属性表达式编辑器”
选择属性为 ConnectionManager 并单击 3 点按钮设置表达式
将弹出“表达式生成器”窗口。从“变量和参数”部分，获取前一步骤中创建的 FilePath 变量并将其拖到“表达式”部分。
点击“确定”关闭“表达式生成器”。再次点击“确定”关闭表达式属性编辑器窗口。

步骤 8：执行并测试应用程序

按 F5 并检查输出

实验 12 - SSIS 中的部署

在进入部署之前，让我们了解 SSIS 项目中部署了哪些元素。如果您查看 SSIS 项目结构，它看起来如下图所示。

最上面是解决方案，解决方案内部是项目文件。此外，每个项目都有扩展名为 DTSX 的包文件，这些包的配置存储在 project.params 中。

要理解 SSIS 部署，我们需要从“什么”和“在哪里”方面进行思考。那么，部署有哪些不同的方式，以及可以部署到哪里？

那么，我们先回答“什么？”。部署 SSIS 项目有两种方式，第一种是项目级别部署，第二种是包级别部署。

当您进行项目级部署时，您会一次性部署所有包。而在包级部署中，部署发生在 DTSX 文件级别。因此，您需要单独部署 DTSX 文件。

项目级部署是新的部署方式，于 SQL 2012 中引入，而包级部署是旧方式，即直到 SQL 2008。在本实验中，我们将更多地关注项目级部署而非包级部署。

我们需要理解的第二件事是“我们可以将这些包部署到哪里？”。换句话说，托管这些包有哪些不同的方式。因此，有 3 个主要的源或宿主，您可以部署到：-

在 SQL Server 服务本身，即在 SSISDB 数据库中。
在 SSIS 服务中：-

- 在文件系统中
- MSDB

因此，为了逐步理解部署，我们将分 5 个步骤进行：-

创建一个简单的文件复制项目，将文件从源复制到目标。
创建该项目的安装程序。
安装包安装程序。
运行前配置包。
最后运行包。

那么，让我们开始这段旅程吧。

步骤 1：创建文件复制项目

因此，让我们创建两个文件夹“Location1”和“Location2”，如下图所示，并在“Location1”中创建一个简单的文本文件“SimpleText”文件。

现在我们想将这个“SimpleText”文件从“Location1”文件夹复制到“Location2”文件夹。为了实现这一点，我们将使用“文件系统任务”控件。因此，创建一个简单的 SSIS 项目并添加一个包“FileCopy.dtsx”文件。您的项目结构应如下图所示。

在此包上，从控制流工具箱中拖放文件系统任务，并将其放在控制流设计器窗格上，如下图所示。

此外，我们还需要提供要复制的源文件和目标文件。因此，右键单击“文件系统任务”控件并单击编辑，然后提供“目标”和“源”连接。因此，在源连接中，我们将指向 location1，在目标中，我们将指向 location2 文件夹。

运行此项目并进行测试，检查文件是否从“Location1”文件夹复制到“Location2”文件夹。

我们特意将此项目保持简单，以便我们可以更多地专注于部署而不是 SSIS 项目。

步骤 2：创建安装程序

如上一节所述，在 SQL Server 2012 中，部署模型现在是项目部署。因此，通过点击“生成”->“全部重新生成”来执行完全生成。现在通过右键单击解决方案并点击“在文件资源管理器中打开文件夹”来进入项目文件夹。

进入 bin/Development 文件夹，您会找到一个完整的 SSIS 安装程序，其文件扩展名为“ISPAC”。双击并运行它。

步骤 3：安装设置

目前，我们将部署到 SQL Server SSISDB 中。因此，连接到 SQL Server 实例，浏览到“Integration Services catalogs”文件夹，右键单击“SSISDB”并单击“创建文件夹”。因此，我们将创建一个名为“MyPackages”的文件夹，并将包部署到其中。