SSIS OLE DB Source is missing rows randomly from an Oracle data source

Question

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.

Details:

My Source is Oracle 19C and Destination is SQL Server 2019.
The connection is an Oracle Provider for OLE DB (64 bits)
Visual Studio 2019 & SSIS 15.0.2000.94
In the OLE DB Source I am using a SQL Command from a Variable
I am logging the records extracted and inserted and I see that the problem is in the extraction.

I have already tried:

Switch between Fast Load // Normal load
Add in the connection string the property UseSessionFormat=True;

I would appreciate any clue or help

Are you certain that something isn't going on in the source database? Some process rebuilding a materialized view or some other batch process that causes your query to return a different set of results than you expect? — Justin Cave
– Justin Cave, Commented Oct 6, 2020 at 16:43

Cristina · Accepted Answer · 2020-10-16 14:58:44Z

0

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

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.

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.

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.

edited Oct 16, 2020 at 14:58

answered Oct 15, 2020 at 15:28

Cristina

1594 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSIS OLE DB Source is missing rows randomly from an Oracle data source

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related