SSIS OLE DB源从Oracle数据源随机丢失行 [英] SSIS OLE DB Source is missing rows randomly from an Oracle data source

问题描述

当从Oracle将数据提取到SQL DB中时，某些软件包遇到一个奇怪的问题:随机获得所有期望的数据集，或者部分获得它，或者得到一个空的数据集.我没有收到任何错误或警告.

I am facing an strange issue with some packages when extracting data from Oracle into a SQL DB : Randomly I get all the expected dataset or I get it partially or I get an empty dataset. I don't get any error or warning.

详细信息:

• 我的来源是Oracle 19C，目标是SQL Server 2019.
• 该连接是OLE DB(64位)的Oracle提供程序
• Visual Studio 2019及更高版本SSIS 15.0.2000.94
• 在OLE DB源中，我正在使用来自变量的SQL命令
• 我正在记录提取和插入的记录，发现问题出在提取上.

我已经尝试过:

• 在快速加载//正常加载之间切换
• 在连接字符串中添加属性 UseSessionFormat = True;

如果有任何帮助或帮助，我将不胜感激

I would appreciate any clue or help

推荐答案

我知道这种方法非常具体，但以防万一它可以帮助某人...

I know that this casuistry is very specific, but just in case it can help someone...

问题的根源取决于我们连接到Oracle的方式.在此新版本(19C)中，必须在查询任何源表以进行提取之前使用调用"secpac.set_user".

The origin of the problem relied on the way we were connecting to Oracle. In this new version (19C) it is mandatory to use the call 'secpac.set_user' before to query any source table to proceed with the extraction.

为此，我们在数据流之前使用了SQL任务.由于连接属性保持相同的连接"，在连接管理器中将其设置为false，我们一直在连续打开和关闭连接，这使流程中断.

For doing that we were using an SQL task before the dataflow. As the connection property "Retain same connection" in the connection manager was setted to false , we were openning and closing the connection continuously and it was making a disfunction in the flow.

解决方案:设置保留相同的连接"属性设置为True.然后，连接仅在第一次需要时打开，并且对于包中的所有任务保持打开状态.

The solution: Set the "Retain same connection" property to True. Then the connection is opened just the first time is needed and remains open for all the tasks in the package.

这篇关于SSIS OLE DB源从Oracle数据源随机丢失行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！