Data Flow inside For Loop Container not behaving as expected.

[Jose]

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?

[Geniusinuse]

En realidad el SSIS puede transferir esa cantidad de registros y más sin
ningún inconveniente.
No hay necesidad de partir tu Dataflow, pues la clase ya viene con esa
particularidad, al manejar
las conexiones, hilos y número de filas en buffer, esto en realidad no
debería demorar más de 20 minutos
en replicarla tu tabla.

Una consideración que puedes utilizar es conseguirte el driver Oracle
Attunity para leer y escribir en Oracle
10 G en adelante, te da mayor potencia a tus queries, debes ver si utilizas
paralelismo y particionamiento
pues eso también te ayudaría.

Saludos
Daniel

Post by Jose
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
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.
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?