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?

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


"Jose" <Jose (AT) discussions (DOT) microsoft.com> escribió en el mensaje de
noticias:89B70268-7415-4727-8FB3-7508448BE8C1 (AT) microsoft (DOT) com...