2022年执行Oracle参数查询文件 .pdf

专家解答修改你的 ActiveX Script任务的最佳方法就是使用脚本组件数据流任务。这个脚本组件允许你在自己的数据流中执行VB.Net代码。当你把一个脚本组件添加到一个数据流中时， 你需要选择下面其中一个选项：如何使用 SSIS中的逆透视数据流转换源 你的代码将从某些地方检索数据，然后把行添加到该数据流中。目标地址 你的代码将从这个数据流的字段中检索数据，然后把这些数据保存在某个地方。转换 你的代码将在这个数据流的字段上做一些转换。要针对一个Oracle数据库执行参数查询， 你需要选择源。 下面是我们作为一个实例建立的数据流：Oracle 参数查询src=http:/ width=414 twffan=done 图一上面数据流的第一个步骤是一个针对Oracle 数据库执行一个参数查询并且添加行到数据流中的脚本组件源。 Row Count 分配行数到一个程序包变量中， Flat File Destination把每行都写到一个文本文件中，这就允许我们和容易看到抽取的数据。我们将把全部的注意力放在配置脚本组件源和我们需要用来从Oracle 检索数据的 VB.Net上。 高效数据检索优化应用程序性能名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 11 页 - - - - - - - - - 下面三个步骤与配置脚本组件源有关：输入和输出 定义要添加到数据流中的输出字段。连接管理器 指定在 SSIS 程序包定义的任何连接管理器，这里的程序包就是我们将在 VB.Net 代码中使用的。脚本- 指定我们在 VB.Net 代码中使用的任何程序包变量并且输入VB.Net代码。输入和输出当你开始编辑脚本组件源属性时， 编辑器中的第一页就是如下显示的输入和输出：图二使用这个页面指定你想添加到数据流中的输出字段。右键单击输出字段来添加字段。在 VB.Net 代码中，你要在某个时间添加一行到数据流中并且指定这里定义的每个输出字段的值。连接管理器连接管理器页面显示如下：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 11 页 - - - - - - - - - 图三使用这个页面来指定你在SSIS程序包中定义的任何连接管理器，这个程序包正是你想在 VB.Net 代码中使用的。填写带有自己选择的标识符的名称字段;你输入的名称将指出你怎样引用VB.Net 代码中的连接管理器。这个连接管理器字段是一个下拉菜单，你可以选择SSIS程序包定义的任何连接管理器。在我们的例子中，我们将写 ADO.Net代码来访问 Oracle 数据库。当你定义这个连接时，可以打开它， 关闭它等等，在代码中让 SSIS程序包解决这些细节是很有意义的。在下面的脚本部分中，我将介绍怎样在代码中访问连接管理器。脚本脚本页面显示如下：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 11 页 - - - - - - - - - 图四在ReadOnlyVariables 或者 ReadWriteVariables中列出你想要访问 VB.Net代码的 SSIS程序包中定义的任何变量。在这个例子中，我们将在我们的SQL语句的 where 从句中使用 v_dept 变量。在这个页面上有一个设计脚本按钮(上面的截图中没有显示 ); 单击它启动应用程序环境的 Microsoft Visual Studio，在这里，你可以输入VB.Net 代码来访问 Oracle 数据库并且配置该数据流中的行和列。现在，让我们写脚本组件源的代码。如下，添加一个引用到System.Data.OracleClient集合中。右键单击引用节点，从内容菜单中选择引用，从可用集合列表中选择集合，接着单击添加按钮。 这里有一些集合引用是自动添加的 ;Oracle 集合并非如此，所以需要你添加。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 11 页 - - - - - - - - - 下面是针对 Oracle 数据库执行参数查询和添加结果到数据流中的VB.Net 代码： Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper STEP 1: add import of OracleClient Imports System.Data.OracleClient Public Class ScriptMain Inherits UserComponent STEP 2: add variables Private connMgr As IDTSConnectionManager90 Private oracleConn As OracleConnection Private oracleCmd As OracleCommand 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 11 页 - - - - - - - - - Private oracleRdr As OracleDataReader STEP 3: add override to get connection Public Overrides Sub AcquireConnections(ByVal Transaction As Object) connMgr = Me.Connections.ORCL oracleConn = CType(connMgr.AcquireConnection(Transaction), _ OracleConnection) End Sub STEP 4: add override to setup the oracle command Public Overrides Sub PreExecute() Dim sql As String = SELECT ENAME, JOB FROM SCOTT.EMP + _ WHERE DEPTNO = :DEPT oracleCmd = New OracleCommand(sql, oracleConn) With oracleCmd .CommandType = CommandType.Text .Parameters.Add(:DEPT, OracleType.Number) End With End Sub STEP 5: Add override to dispose of the oracle command Public Overrides Sub PostExecute() If Not oracleCmd Is Nothing Then oracleCmd.Dispose() End If 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 11 页 - - - - - - - - - End Sub STEP 6: Add override to release the oracle connection Public Overrides Sub ReleaseConnections() connMgr.ReleaseConnection(oracleConn) End Sub STEP 7: Add code to execute the query and add the results to the data flow Public Overrides Sub CreateNewOutputRows() Dim DEPT As Integer = Me.Variables.vdept With oracleCmd .Parameters(:DEPT).Value = DEPT End With oracleRdr = oracleCmd.ExecuteReader() While oracleRdr.Read() OutputBuffer.AddRow() OutputBuffer.EmployeeName = oracleRdr.GetString(0) OutputBuffer.Job = oracleRdr.GetString(1) End While OutputBuffer.SetEndOfRowset() End Sub End Class 下面是上面显示的代码要点：STEP 1 添加一个输入语句，因此我们可以引用System.Data.OracleClient命名空间中的类而不用指定全名; 例如， 在步骤 2中，名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 11 页 - - - - - - - - - oracleConn 变量被定义成一个OracleConnection类型; 没有输入语句，它可能被定义成 System.Data.OracleClient.OracleConnection。STEP 2 定义在整个代码中使用的一些变量。STEP 3 包含代码来使用在 SSIS 程序包中定义的 Oracle 连接管理器。STEP 4 建立 oracleCmd 变量，指定包含 DEPT 值的参数的查询。注意，这个参数在查询中是以冒号开头的; 这是 Oracle 要求的一个语法。你可以有多个参数; 只需把它们添加到这个查询中并且如下调用Parameters.Add 方法。STEP 5 和 STEP 6 两个都是清除函数。STEP 7 包含执行查询和添加返回到数据流的每行的代码。oracleCmd 和oracleRdr 变量在步骤 2 中定义。请注意， vdept 变量用来提供在步骤4 中定义的 DEPT 参数值。ExecuteReader 方法在你想要执行返回行的查询时使用; 它返回一个 OracleReader 。Read方法被调用来读取结果集中的每行。对于读取的每行，我们通过调用 OutputBuffer中的 AddRow 方法然后把值分配给在脚本组件源的输入和输出页面中定义的每个输出字段来把一行添加到数据流中。SetEndOfRowSet方法在 OutputBuffer中调用，从而指出我们完成了添加行的过程。请注意，这里的代码量是很大的，但是主要部分对于任何查询都是相同的。对于大多数来说，只有 STEP 4 和 STEP 7需要基于运行的查询进行修改。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 11 页 - - - - - - - - - SQL 2005的 SSIS与 Oracle 的迁移性能技术类 2007-09-06 18:09 阅读 19 评论 0 字号：大中小作者：不详来源：转载项目中存在一部分数据迁移的工作，说白了就是从老的系统中将数据倒换的新的系统模型中，老系统的数据来源比较复杂多样，新的自然是Oracle9.2 。本来这也就是一次性工作，用SQL 自然是最快的方式，不论是开发还是数据传输的速度。可是甲方偏偏要看到界面，希望这是一个成型的工具，没办法，甲方就是上帝。公司原来也有一个迁移工具，可是只能适用于表对表的倒换，复杂一些无能为力，而且数据还巨慢，用过的人都是对它无语。从新开发，不说花费和效果，光是时间也不行。没办法，只好看看现在流行的ETL 的工具。市场前列毋庸置疑，肯定是Informatia 和 DataStage. Informatia 没有，只好看看DataStage 是否能适应现在的功能要求。不想，虽然是图形界面，可使用起来一点也不容易，而且安装后，Windows下居然不能脱离域环境，而且不是Server 版本的Windows还不能运行Paralle Job 。郁闷无比。试了两天后，暂时放下。Microsoft 的易用性比功能强大更吸引我。试试SQL Server 2005 中的 SSIS，号称企业级的ETL 。一用之后呢，没想还真有点喜欢上了它，从介绍的和界面上看一点也不比DataStage的功能少，性能，哈，下面就是我要说得了。ETL 工具最慢的部分都是L 这一部分，按照一般的说法能占到总体时间的五分之四，所以这是关键。测试也不算复杂，就是同样的数据抽取、转化、然后加载用不同的驱动分别跑一遍，目的库已经确定是 Oracle ，所以也没有太大的余地了。在 SSIS 中，有两个驱动可以连接Oracle 数据库，一个是Microsoft OLEDB Provider for Oracle ，另外一个是Oracle Provider for OLEDB 不测不知道，还真长了不少见识。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 11 页 - - - - - - - - - 同一机器，同一数据源，同一结果，两者间还真有不少区别。首先是速度（连续三次）：Microsoft OLEDB Provider for Oracle 1 分 37 1 分 32 1 分 30 Oracle Provider for OLEDB 1 分 10 1 分 07 1 分 02 在速度上Oracle Provider for OLEDB 基本符合1 分 3 万条左右，而Microsoft OLEDB Providerfor Oracle 1 分钟只有 2 万条左右。照这样看，答案似乎也就出来了，Oracle Provider for OLEDB 也就成了不二选择。且慢，我还没有说明为什么选择25 万条记录而不是别的数量的数据呢。这就不得不说说内存的使用：未启动数据迁移时即停留在VS.Net 设计界面时，内存已使用了790M左右，而我机器的物理内存也就896M 。运行开始后， 25 万条记录下Microsoft OLEDB Provider for Oracle 平均在 1G 左右，而 Oracle Provider for OLEDB 乖乖得不得了，铁定在1.25G 以上，一次还在1.3G 。更离谱的是，原数据表中共有近100 万条记录， Microsoft OLEDB Provider for Oracle 在内存峰值1.5G 左右可以顺利完成，而Oracle 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 11 页 - - - - - - - - - Provider for OLEDB 在内存使用一旦突破1.3G 往上一些，就开始不停提示内存不足，不在安心的迁移数据了，或者干脆显示为红色，报一些莫名的错误。这就让人两难了，一个速度快了那么50% ，可确是一个内存消耗大户，有没有止境，我这破机器也无从得知。另外一个速度慢，可却节俭持家，穷人也照顾到了，哈。感觉好这有点像Oracle 和 MS 的企业风格，一个走高端，为了需要的指标可以不计成本，穷人靠边；另一个呢，还不错，虽然也越来越来不鸟没钱的人，可还做得不太显眼。最后了，同样的数据源（Microsoft OLEDB Provider for Oracle 驱动），将目的库换成SQL Server 2005,驱动为 SQL Native Client, 同样的数据数据转换，98.9 万条记录中11.1 万条入库，靠1 分 12 完事，打开 FastLoad ，58 秒搞定。而且都只是第一次运行，相信如果多运行几次后，结果应该更好。别说，自家孩子真就不一样，别人的家的没法比。由于数据库驱动接触并不多，希望那个大虾指点一下，能帮忙给找一个Windows下 Oracle 驱动可以媲美与 SQL Native Client 的，先谢了。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 11 页 - - - - - - - - -