Data Flow Task Inside For Loop Container not behaving as expected.

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

  1. Jose

    Jose Guest

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