Data Flow inside For Loop Container not behaving as expected.

Discussion in 'microsoft.public.sqlserver.dts' started by Jose, Jun 22, 2010.

  1. Jose

    Jose Guest

    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?
    Jose, Jun 22, 2010
    1. Advertisements

  2. Jose

    Geniusinuse Guest

    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.

    Geniusinuse, Jun 30, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.