使用 SSIS 调用 Web 服务，然后存储和查询结果

简介

了解如何利用 SQL Server Integration Services 完成依赖于远程数据源的数据任务，这往往会带来巨大的好处。本教程将介绍一个看似简单的问题及其解决方案，该方案涉及从 SQL Server Integration Services 包中调用 Web 服务，将 XML 存储在 SQL Server 数据库表中的 XML 类型列中，然后使用 XQuery 提取数据。请注意，Web 服务也可能返回 JSON，我将在稍后的教程中对此进行解释和介绍。这种简单的方法和解决方案可以重复用于收集几乎任何公共 Web 服务的信息。请谨慎并有目的地使用此方法。

我需要解决的问题是，从不完整的地址数据中获取城市和州，而唯一一致的信息是邮政编码的存在。解析 string 以获取 city 和 state 被迅速排除，因为 city 名称可能包含两个单词，例如“Traverse City, MI”。当然，邮政编码是从 string 中提取的，这仅仅是因为该数据位于每个地址的末尾，并且是 5 位数字，或者 5 位数字加上 4 位数字，用连字符分隔。

本教程面向中高级数据库或 SSIS 开发人员。我包含了您应该已经知道的基础概念的链接。

以下是一个示例，展示了我从一位商务人士发来的 Excel 工作表中的几行数据。出于保密原因，实际的联系人姓名和公司名称以及地址已被更改。

步骤

1. 将 Excel 中的地址数据导入到新表或现有表中。
2. 向该表添加一个列，或者添加一个新列来存储邮政编码。 转到第 3 步。
3. 创建一个存储过程来存储来自 Web 服务调用的 XML。 转到第 4 步。
4. 创建一个 WSDL 文件。 转到第 5 步。
5. 构建并运行 SSIS 包。 转到第 6 步。
6. 使用 XQuery 查询 XML 数据。 转到第 7 步。

入门

第 1 步：将地址数据导入表

除非您打算保存和重用此组件，否则只需使用 SQL Server Management Studio 的导入和导出向导。为简便起见，此处不包含完成此任务的详细步骤。

第 2 步：向该表或其他表添加列以存储邮政编码和 XML

向该表或新表添加一个列，以存储邮政编码和从 Web 服务调用返回的 XML。有关 XML 数据类型的信息，请参阅 在 SQL Server 中实现 XML。

第 3 步：创建一个存储过程以获取所有邮政编码

使用存储过程是一种最佳实践，如果还没有养成习惯，请养成。

第 4 步：创建一个存储过程来存储 Web 服务调用的 XML

在尝试插入新行之前，有多种方法可以先检查是否存在现有行。MERGE 语句非常强大，并且可以以极高的效率编写！在此处阅读有关 MERGE 语句的更多信息：MERGE (Transact-SQL)。考虑到我不会将此代码用于生产环境中的重复使用，为了简单起见，我使用了 IF NOT EXISTS ...

第 5 步：创建一个 WSDL 文件

例如，创建一个名为 getcitybypostalcode.wsdl 的空 WSDL 文件。SSIS 包将在第 7 步中使用此文件。有关 Web 服务描述语言 (WSDL) 文件的更多信息，请参阅 理解 WSDL。

在您的工作目录中，右键单击，单击“新建”，然后单击“文本文档”。按名称命名文件（例如，getcitybypostalcode.wsdl）。

第 6 步：构建并运行 SSIS 包

第 6.1 步

对于这个项目，我使用的是安装了 Update 3 的 Visual Studio 2015。创建一个新的 Integration Services 项目，并将其命名为“调用 Web 服务”。

第 6.2 步

添加一个新的连接管理器以连接到您的 SQL Server 实例和数据库。出于此处未解释的各种原因，我选择使用 ADO.NET 连接管理器。自 SQL Server 2012 起，OLE DB 已被弃用。有一些很棒的博客文章比较了 ODBC、OLE DB 和 ADO.NET 连接管理器。如果您对该主题感兴趣，这里有一篇：链接：点击此处。

第 6.3 步

添加包变量 city、postalCode 和 postalCodes，并设置以下数据类型。

第 6.4 步

配置一个 Execute SQL Task 来调用 dbo.pr_GetPostalCodes 存储过程。

第 6.5 步

将 Execute SQL Task 的结果集映射到 postalCode 变量。将 Result Name 设置为零（“0”），如图 12 所示。

第 6.6 步

添加一个 Foreach Loop Container，然后配置 Collection 和 Variable Mappings。

第 6.7 步

配置 Foreach Loop Container 的 **Collection** 设置。

第 6.8 步

配置 Foreach Loop Container 的 Variable Mappings。对 postalCodes 对象中的每个邮政编码使用 postalCode（单数）。

接下来，添加 Web Service Task，该任务将配置为为每个邮政编码调用 GetInfoByZip Web 服务。每次调用的 XML 结果将存储在数据库中。让我们看一下 Web 服务。

第 6.9 步

检查并尝试使用公共 Web 服务 GetInfoByZip，该服务在编写本文时是一个免费且公共的 Web 服务，它按邮政编码返回州代码、城市、区号、时区和邮政编码（以 XML 格式）。还有其他几种可用方法。

第 6.10 步

创建一个新的 HTTP Connection Manager。

1. 在 Connection Managers 窗格中右键单击，单击“New Connection…”，然后选择 HTTP。
2. 单击“Add…”
3. 输入用于获取 Web 服务 WSDL 的 Server URL：http://webservicex.net/uszip.asmx?WSDL，然后单击“OK”。

第 6.11 步

将 Web Service Task 添加到 Foreach Loop Container。在接下来的步骤中，您将下载并保存 WSDL 文件，并配置 Web Service Task 的 Input 和 Output 设置。

1. 在“General”选项卡上，对于 HttpConnection 属性，选择在上一步中创建的 HTTP Connection Manager。
2. 对于 WSDLFile，单击省略号按钮，然后浏览到第 5 步中创建的文件。
3. 将 OverwriteWSDLFile 属性设置为 True。
4. 单击“Download WSDL”按钮。您应该会看到一个消息框，显示“已成功下载指定的 Web Services Description Language (WSDL) 文件。”（参见下面的图 19）。

第 6.12 步

配置 Web Service Task 的 Input 和 Output 属性。

1. 在“Input”选项卡上，将 Service 属性设置为 USZip。
2. 将 Method 属性设置为 GetInfoByZip。
3. 单击 Variable 列中的 checkbox 将 Value 列切换为下拉列表。
4. 选择 User::postalCode。
5. 在“Output”选项卡上，使用下拉列表将 OutputType 设置为 Variable。
6. 使用下拉列表，将 Variable 设置为 User::city，然后单击“OK”。

第 6.13 步

添加另一个 **Execute SQL Task**，并配置它来调用 dbo.pr_AddCity 存储过程。相应地配置 Web Service Task 和 Execute SQL Task 之间的顺序。

第 6.14 步

配置 Execute SQL Task 的“General”选项卡。

第 6.15 步

配置 Execute SQL Task 的 Parameter Mapping 属性。这是运行包之前的最后一步。然后我们将使用 XQuery 查询数据。

1. 单击“Add”按钮。
2. 从 Variable Name 下拉列表中选择 User::city。
3. 将 Data Type 选择为 XML。
4. 输入“city”（不带引号）作为 Parameter Name，然后单击“OK”。

第 6.16 步

在运行包之前，为了让包快速完成，我建议只从 dbo.pr_GetPostalCodes 存储过程中返回 10 行。运行包。

第 7 步：使用 XQuery 查询 XML 数据

检查 dbo.WebServiceData 表中 Web 服务调用的结果。单击任意一行，在另一个选项卡中查看 XML，如图 27 所示。

现在，使用 XQuery 从其元素中提取数据！这里有两种不同的 XQuery 方法。

返回顶部

成果

在撰写本教程时，我意识到当我专注于教授某事时，信息就会变得根深蒂固。这是我的第一个教程！截至 2017 年，我作为 Microsoft SQL Server 专家已有 17 年多的经验。在 SQL Server 之前，我花了大约 5 年时间使用 Microsoft Access 开发数据库。如果您正在阅读本文，我鼓励您写下您感兴趣的内容。这个过程会让你在任何事情上都做得更好。

历史

• 2017 年 4 月 17 日：初始版本