SQL Server集成服务用于Oracle Database 10g

核心提示：1. 导言这篇文章关注于使用SQL Server集成服务来从异构数据源提取数据以及将数据导入到Microsoft® SQL Server™用于商业智能(BI)分析和报表的好处，Oracle Database 10g数据作为主要数据源来使用，SQL Server集成服务用于Oracle Databas

1. 导言

这篇文章关注于使用SQL Server集成服务来从异构数据源提取数据以及将数据导入到Microsoft® SQL Server™用于商业智能(BI)分析和报表的好处。Oracle Database 10g数据作为主要数据源来使用。

这篇文章的读者包括IT专家、数据库管理员和系统架构师。读者应该具有对数据库和Microsoft SQL Server以及Oracle Database 10g的一般性了解。读者应该在他们喜欢的硬件平台上使用参考的数据库。

SQL Server是Microsoft集成商业智能平台的一部分，并覆盖了数据仓库、分析和报表、记分卡、计划和预算。SQL Server在甘特的商业智能平台的魔力象限和数据仓库的魔力象限中都是处于领导者象限。Microsoft在SQL Server标准版和企业版中都具有优秀的商业智能产品。这些包括SQL Server集成服务(SSIS)、SQL Server报表服务(SSRS)、以及SQL Server分析服务(SSAS)。与之对应的，Oracle提供了类似的功能作为Oracle企业版的可选项，但是需要额外收费。

这篇文章首要关注的是SQL Server 集成服务。SSIS提供了对异构和同构环境的支持，并作为一个集成工具服务于使用多个数据源和运行在Microsoft和非Microsoft软件环境中平台的客户。我们介绍了建立具有一个异构数据源的SSIS和导入数据到SQL Server是多么简单。我们还记录了从一个Oracle Database 10g数据源导入数据到SQL Server的步骤。

许多IT经理努力采用实用的、性价比高的解决方案来支持他们的业务过程。我们再次强调SQL Server 所带给商业智能解决方案的价值。SQL Server 包括优秀的商业智能工具而没有额外收费，这是IT经理所不能忽视的。

2. 关于SSIS

SQL Server集成服务(SSIS)是建立在Microsoft SQL Server之上的首要数据转换框架。它执行许多任务，从简单的导入/导出操作到在异构数据源间复杂的高性的提取、转换、加载(ETL)任务。 这个强大的功能来自于一组紧密结合的工具套件，它包括控制流和数据流逻辑设计器、用来建立和执行独立的包的工具，以及支持这个执行和自动化高性能数据转换所必需的服务。

SSIS来源于SQL Server 的之前版本，是作为数据转换服务(DTS)——一个建立在SQL Server之中的简单数据转换框架。

SSIS作为数据转换服务(DTS)——一个内置于SQL Server之中的简单转换框架，出现于SQL Server的先前版本。DTS所缺少的是广泛的功能，它只是以一个简单的“希望完成它”的思想来发布的。这使得开发人员和数据库管理员可以在Microsoft SQL Server和其它数据库平台或数据源间直接提取、转换和加载数据。但是，如同数据库操作的需求改变了一样，对SQL Server中一个更加复杂的ETL解决方案的需求也改变了。

SSIS是许多年来客户反馈和Microsoft修改的最终结果。在SQL Server中数据转换的基本前提没有改变，但是完成ETL任务的工具和处理从根本上改变了。SSIS具有比它之前版本更加广泛的功能。这篇文档提供了一个使用SSIS从外部数据源提取和转换数据到一个SQL Server数据库的实际例子。这个例子中的数据源来自于一个Oracle Database 10g数据库。随着Oracle and SQL Server之间所有者的总成本的增加，这个例子可能变得更通用。IT决策制订者在决定使用哪个数据库平台来运行他们的业务时将成本看作是一个重要的购买依据，而且SQL Server具有建立商业智能解决方案所必需的程序。

开始

在我们开始我们的ETL过程示例之前，我们必须先定义源Oracle数据库和目标SQL Server数据库之间的联系路径。这要求安装所必需的Oracle支持软件。Oracle要求网络传输工具比如Oracle Net来与其它的数据库服务联系。Oracle Net与SQL Server Tabular Data Stream(TDS)很类似。Oracle Database 10g客户端软件最新的32位和64位版本可以从这里下载：

http://download.oracle.com/otn/nt/oracle10g/10201/10201_client_win32.zip

http://download.oracle.com/otn/nt/oracle10g/10201/102010_win64_x64_client.zip

要注意安装客户端软件的特定版本——32位或64位。为你的操作系统(32位或64位)安装正确的版本。

2.1 安装Oracle Database 10g客户端软件

在你下载和提取客户端软件档案文件之后，进到存储的安装文件所在目录，然后进行下面的过程。

要安装Oracle Database 10g客户端软件

1. 双击 setup.exe文件。这会启动Oracle Universal Installer。会出现一个类似于下面图片的欢迎界面。选择Custom，然后点击Next。

2. 下一个界面提示为安装输入一个名称和目录地址。如果必要的话，编辑这个信息。然后点击Next继续。

3. 现在要安装的组件。除了默认的选择，确保还选择了Oracle Windows Interfaces和Oracle Net。点击Next。

4. 下一个界面显示了产品特定先决条件的检查情况。确保所有的检查都成功，如果必要的话修正所有的问题。然后点击Next。

5. 在提示输入用于Microsoft Transaction Server的Oracle Services端口号页面(图5)里，接受默认端口号，点击Next。

6. 一个总结界面显示了Oracle Universal Installer将要执行的所有任务。检查这些信息是否正确。点击Install来开始软件部署。

7. 在软件部署完成之后，Oracle Net Configuration Assistant将开启并指导你进行配置Oracle Net软件的配置过程。按你的环境配置设置。下面的选项是用于这个例子的：

选项	值
Perform Typical Configuration	No (unselected)
Selected Naming Method	Local Naming
Service Name	ORCL
Network Protocol	TCP
Host Name	ADAMS
Port Number	1521
Perform Test	Yes
Net Service Name	ORCL

8. 当Oracle Net Configuration Assistant完成时，Universal Installer会显示这个安装完成了。点击Exit来关闭这个安装。

2.2 测试Oracle Database 10g客户端的安装

在安装了客户端软件之后，可以通过执行一个从Oracle导入基本数据到SQL Server的操作来测试和验证下这个安装。

为了测试Oracle Database 10g客户端安装

9. 从SQL Server管理套件中，右键单击你可以用来执行一个测试导入的数据库，选择Tasks，然后选择Import Data。

10. 在指定数据源的提示中，改变Data Source选项为Microsoft OLE DB Provider for Oracle。点击Properties按钮。

11. 在数据链接属性对话框中，输入字段信息。对于服务器名称，使用你在之前安装客户端软件的过程中第七步输入的Net Service Name。确保你提供的凭证可以访问Oracle 中的示例数据。可选的，你可以执行一个连接测试，点击Test Connection。当完成的时候点击OK。

12. 下一个界面提示数据导出的目的地。默认是你在第一步中指定的数据库。检查信息是否是正确的，然后点击Next。

13. 下一个界面告诉你指定你是否想在运行一个查询时从一个表或视图拷贝数据。选择从一个或多个表或视图拷贝数据，然后点击Next。

14. 选择从数据源导入的表或视图。确保你指定的目标表不存在。点击Next。

15. 在提示执行导入或保存它为一个包的页面中，选择导入，然后点击Next。

16. 在总结页面中，检查这些信息是正确的，然后点击Finish来开启这个导入过程。

17. 一个显示执行步骤和状态的对话框会显示出来，如下所示。

在这个过程中，你可能会遇到类似于下面的警告。这是预料当中的，并且你可以忽略它们。如果出现了警告对话框，点击OK关闭它。

18. 当安装完成的时候，点击Close来退出这个向导。

如果一切正常，并且没有致命错误，那么这个安装就是成功的。这表示所有的软件组件都工作正常，而且SQL Server 可以可靠地从Oracle提取数据。下一步是建立一个可重用的SSIS数据导入解决方案。

3. 在SSIS中建立一个ETL解决方案

3.1 计划这个解决方案

当开发一个全面的解决方案时，第一步总是制定计划。在计划过程中要回答的问题有：

· 这些数据从哪来?

· 这些数据要到哪去?

· 数据源和目的地之间发生了什么?

这些步骤可能看起来都很基础，但是它们是在进行任何开发工作之前要定义的重要对象。清楚的对象定义使得消除了可能会破坏一个成功ETL解决方案的任何不确定因素。

在我们的例子中，我们从一个Oracle事务型系统提取了销售订单信息并将它导入到一个我们正在建立的SQL Server数据仓库中。这个例子开始于一个从Oracle到SQL Server的简单数据拷贝。然后随着数据仓库的发展添加一些简单的数据转换。

3.2 创建解决方案

所有的SSIS解决方案开始于SQL Server商业智能开发套件(BIDS)。这个集成的开发环境是作为开发任何商业智能解决方案的中心，无论它是一个集成服务包、一个报表服务报表，或是一个分析服务立方体。要开始，启动BIDS并创建一个项目。

从开始菜单打开BIDS。对于项目类型，选择Integration Services Project。命名这个项目，然后选择默认地址或指定一个不同的地址。点击OK。图11显示了一个新项目对话框例子。

在新项目创建之后，你会看到用来创建和定义项目工作流的用户界面。这个工作流分为三个主要分类：控制流、数据流和事件处理。

控制流定义什么类型的任务将作为这个包的一部分来执行。任务可以包括简单数据流和批量插入任务、包括备份和数据库完整性检查的维护计划、还有更多的复杂立方体处理任务、e‑mail任务，或文件系统任务。这些广泛的可用控制流任务显示了SSIS的灵活性。

数据流任务包括基本的数据导入和导出，以及数据合并(可用于连接从异构数据源获得的数据)、数据转化和转换，以及复杂的模糊数据搜索和分组。

事件处理使得你可以创建独立于主要包的控制流逻辑，它只在包遇到一个定义的错误事件时才执行。

要创建一个包，你首先要定义控制流逻辑。.

要定义控制流逻辑

1. 对于我们的例子，从左边的工具箱拖拉一个Data Flow Task到控制流中去，如图12所示。

2. 要给这个控制流任务添加必要的逻辑以支持数据导入，双击Data Flow Task图标以显示你可以添加数据流任务的数据流画布。

3. 要定义源和目标数据源，右键单击窗口下面的Connection Managers面板并选择New OLE DB Connection。

4. 在显示的对话框中(看图13)，选择New。对于Provider，选择Microsoft OLE DB Provider for Oracle。点击OK完成定义数据源连接和关闭Connection Manager对话框。

新创建的连接显示在Connection Manager面板中。

5. 要为目标连接打开一个新的连接管理器，右键单击Connection Manager面板并选择New OLE DB Connection。点击New。

6. 在Connection Manager对话框(图14)中，provider选择Native OLE DB/SQL Native Client。输入服务器名称、用户凭证和目标数据库名称。点击OK来完成定义目标连接和关闭Connection Manager对话框。

你应该看到你的两个连接都显示在Connection Manager面板中。

现在，定义从源到目标数据源的数据转移。

要定义数据源中的数据怎样移动到目的地

1. 从工具箱中，拖拉一个OLE DB Source数据流来源和一个SQL Server Destination数据流目的地到数据流画布中。

2. 点击OLE DB Source图标并拖拉绿色箭头到SQL Server Destination。这在逻辑上连接了来源和目的地。

3. 双击OLE DB Source图标以显示来源属性。选择你之前为来源连接定义的连接管理器，然后选择你想提取数据的来源表。你的屏幕的显示应该类似于下面的图片。点击OK来关闭这个对话框。

4. 要阻止在我们之前测试导入中遇到的默认代码页面监测警告，那么改变OLE DB Source对象的AlwaysUseDefaultCodePage属性为True。

5. 双击SQL Server Destination图标打开目标属性。选择你之前为目标数据源定义的连接管理器，然后选择要导入到目标数据源的表。如果你还没有创建目标表，你可以现在创建，通过点击New。你的窗口看起来应该类似于下面的图片。

你可能注意到在对话框下面的警告信息。这表示我们没有提供源字段到目标字段的逻辑映射。SSIS将自动努力进行一个“最佳”映射尝试，但是如果这个映射是比较复杂的，那么这可能需要用户的干预。在我们的例子中，有一个从源字段到目标字段的直接映射。换句话说，源字段和目标字段是一致的。

6. 要浏览这个映射，那么点击左边的Mappings选项。看图17。

7. 检查映射是否正确，然后点击OK。现在这个数据流画布应该如图18所示。

注意，在实际场景中，你可能只想匹配源字段的一个子集到目标字段。

现在配置好了基本的数据导入，可以测试了。

要测试数据导入

1. 要保存这个项目，从File菜单选择Save All。

2. 从Build菜单，选择Build [Project Name]。

3. 从Debug菜单，选择Start Debugging。

这从BIDS开发环境中快速地执行了这个包，并将这个包的执行状态报告回来，如下面的图19所示。

绿色表示成功执行。转移的记录数显示在绿色连接箭头的下方。在这个例子里，所有的任务都成功完成了，而且转移了正确的记录数。

3.3 改进这个解决方案

我们的例子到目前为止，只介绍了一个非常简单的源字段到目标字段的直接映射而没有执行实际的转化。现在我们将这个例子更进一步，添加一些基本数据转化。

数据转化

在我们的例子数据中，有四个字段显示了美元数量(SubTotal、TaxAmt、Freight和TotalDue)，但是现在它们被配置为导入普通数字型数据类型而不是货币数据类型。我们需要命令SSIS将这些数据作为货币导入。首先，改变目的表来反映这个数据类型的改变，然后插入一个数据转化任务到这个数据流中去。

为了添加一个数据转化任务到数据流中去

1. 右键单击OLE DB Source和SQL Server Destination之间的绿色连接箭头，然后选择Delete。

2. 从工具箱里，拖拉一个数据转化任务到数据流画布里。从OLE DB Source到Data Conversion图标连接绿色箭头。从Data Conversion图标到SQL Server Destination图标连接绿色箭头。

3. 双击Data Conversion Task来打开任务属性。这个对话框是你定义数据类型转化的地方。

4. 在Available Input Columns列表中，选择每一个要求数据转化的字段。这些被添加到窗口下面的面板中。这创建了到数据流的输入字段逻辑拷贝。可选的，你可以通过编辑Output Alias属性字段来重新命名这个逻辑拷贝。

5. 将数据类型从数字型[DT_NUMERIC]改为货币型[DT_CURRENCY]，然后点击OK。下面的图片是一个例子。

6. 双击SQL Server Destination任务图标打开任务属性。点击窗口左边的Mappings选项。

7. 光标停在映射线上并点击Delete从而删除源输入字段和目标字段间的已有映射。

通过拖拉新的字段别名到目标字段从而在源字段和目标字段间创建新的映射，直到你的屏幕显示类似于图21所示。

8. 完成时点击OK。这时数据流画布应该如图22所示。

9. 当你完成了之前的操作后保存、建立和开始调试这个包。在这个包完成之后，输出窗口开起来应该如下所示：

你现在已经成功地添加了一个简单的数据转化到你的导入包中。结果是输出字段具有正确的货币数据类型而不是一个普通的数字数据类型。这个基本例子可以扩展在其它场景中执行相对复杂的数据转化。

出列

现在我们要做些更复杂的事情。我们在源数据里有一个字段叫做OnlineOrderFlag，它指定了一个订单是否在线。这是一个数字型字段作为一个比特字段来使用，存储0代表False而1代表True。我们想创建一个更具亲和力的字段形式，其中存储的是“True”或“False”字符值而不是比特字段。我们可以通过一个出列数据流任务来进行这项工作。SSIS将为基于一个我们提供的表达式的逻辑字段获取一个值。要指定获取值的数据源，这个表达式可以参考输入流的其它字段或代码值，并且具有所有的逻辑级别，从简单到复杂。

第一步是添加一个新的字段到我们的目标表中用来存储新获取的值。

要添加一个出列

1. 右键单击Data Conversion图标和SQL Server Destination图标之间的绿色连接箭头，并选择Delete。

2. 从工具箱拖拉一个Derived Column数据流任务到数据流画布中。从Data Conversion任务到Derived Column任务用绿色箭头连接，然后从Derived Column任务到SQL Server Destination任务用绿色箭头连接。

3. 右键单击Derived Column任务图标以显示任务属性。

4. 添加一个新的出列、表达式和数据类型，它们的属性类似于下面图片所示。

这个表达式是一个类似的替换。它检查了OnlineOrderFlag字段看看它的值是否是“1”，如果是的话那么替换是“True”。否则，这个字段会显示为“False”。这些新的值将存储在一个叫做OnlineFlagChar的出列字段中，它是一个可变长度的字符字段。点击OK保存这个出列字段设置。

5. 右键单击SQL Server Destination任务打开任务属性。点击窗口左边的Mappings标签。

6. 下拉输入字段的字段映射列表直到你找到新的出列字段。将它拖到你目标字段的新字段中，如下图所示。

7. 完成的时候点击OK。你的数据流画布看起来应该如下图所示：

8. 保存、建立和启动调试这个包，如同你先前所做的一样。在这个包完成之后，输出窗口应该如图27所示。

你现在已经添加了一个出列到你的导入过程。出列为SSIS的数据流能力提供了强大的功能。它们不局限于我们在例子中描述的简单替代。

4. 总结

这些简单的示例只说明了SQL Server集成服务的一小部分功能。

对于那些之前有数据转换服务(DTS)经验的人可以很清楚SSIS提供了更好的灵活性和功能。更熟悉Oracle的人可能会惊讶于Microsoft竟在SQL Server中包含了这样强大的免费软件。但是Microsoft是致力于为它们的优秀数据库平台提供最好的商业智能解决方案。