Hi!
I have a table with 3M records on an ORACLE DB. A record can fit about 300K
Bytes. Don't ask me why, this was not my design.
I am trying to replicate the data from one DB Server to another DB server.
SSIS fails when getting to record 80K. Error: ROW - 00060, Datainsert.
I understand this is a problem between ORACLE and SSIS. So, after
researching, one Idea mentions to create a loop container and insert the data
in a specific number of records.
So, I decide to use a For Loop Container with a Data Flow Task. Inside the
Data Flow Task there is an OLE DB Source and an OLE DB Destination controls.
I made the logic to insert the data in a group of 60K records on each
iteration of the For Loop Control.
The Ole DB Source control uses a Variable Value to get the data, I build
this query using a Script Task :
Select * from (Select * from table where ROWNUM between 1 and 60000).
This is ORACLE 11g so, using the between statement it is working. I first
tried this logic on a Stored Procedure moving the data to the same DB server
with a different table name and it worked.
The problem consist that the Data Flow Task only works the first time. After
debugging I can see the Query Variable is changing according to the logic.
The second time it loops, the query looks like this:
Select * from (Select * from table where ROWNUM between 60001 and 120000)
But I don’t see the Data Flow Task working after the first Iteration of the
For Loop. The For Loop Container does Loop 50 times, but I only get the first
60K records on the Destination Table.
Anybody has an idea or a work around to my problem?