Data Replication 2
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Package Hanging

  1. #1
    oldjeep is offline Junior Member
    Join Date
    Oct 2009
    Posts
    9
    Rep Power
    0

    Package Hanging

    I've got a simple package that basically moves 31 million rows from one table to another. The source has a join to one table to grab an ID value. The package is using the Oracle Source and Destination controls by Attunity that you can download from Microsoft.

    The 31 million rows complete in about 8 hours, which is fine. However the destination block doesn't seem to think it is finished. I ran it in BIDS last night to see what was going on, 16 hours later it is just sitting on the destination block (Yellow indicating it is running) . If I go to the DB, the records are all there. If I kill the package, the records are still all there.

    The Oracle Source and Destination are working fine in the previous step which loads 300K records into a different table. Destinations are set to Fast load with no logging, same results occurred with Fast Load and logging turned on.

  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    Does it also 'not complete' when you run the package in DTExec outside of BIDS?
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    oldjeep is offline Junior Member
    Join Date
    Oct 2009
    Posts
    9
    Rep Power
    0
    Quote Originally Posted by DrorHarari View Post
    Does it also 'not complete' when you run the package in DTExec outside of BIDS?
    Yes, the same thing happens when running via DTExec. On further inspection all of the indexes on the table were marked as unusable after the process ran and was terminated. Will the package not return until all of the indexes update? With SQL Server that wouldn't be the case, but I'm not very familiar with Oracle.

  4. #4
    colton is offline Junior Member
    Join Date
    Oct 2009
    Posts
    5
    Rep Power
    0
    Did you find any resolution to this? I have a similar issue. My package was running fine for a few weeks, but then just a few days ago (Oct 1st, same day as your post) it started behaving like you said. I have a few different data flows that pull from different sources and all store to the same oracle destination. All were running fine, but now are hanging at the destination waiting to finish, though the record count shows all has shipped.

  5. #5
    oldjeep is offline Junior Member
    Join Date
    Oct 2009
    Posts
    9
    Rep Power
    0
    Not yet, I havn't been back to that client this week. Their Oracle folks were supposed to do some digging and find out why all the indexes wound up marked as unusable.

    Chuck P
    Chucks 46 CJ2a

  6. #6
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    10
    Hi,

    When setting the Oracle Destination component to fast load, it uses the Oracle Direct Path protocol to load the destination component.

    During Direct Path loading, Oracle ignores referential integrity constraints and, at the end of the loading session, Oracle performs index maintenance on the table if it has indexes.

    You can avoid the performance impact of index maintenance by dropping the index before loading the destination table and then rebuilding it afterward.

    Regarding indexes marked UNUSABLE, this may be related to the fact that if errors occur during Direct Path load, some indexes could be marked UNUSABLE at the end of the load.


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    colton is offline Junior Member
    Join Date
    Oct 2009
    Posts
    5
    Rep Power
    0
    I have not been using the fast load option. The weird thing is that the package has been running for a couple months and was running in 6 minutes. I'm moving roughly a million rows, from a few different sources to a single destination. On Oct 1, the package went from running in 6 minutes to over 5 hours. There have not been any changes, on either the package, the source or the destination. I am still trying to fix this issue, and will try to look into the index rebuild.

  8. #8
    colton is offline Junior Member
    Join Date
    Oct 2009
    Posts
    5
    Rep Power
    0
    So we are still trying to figure this out. The only thing we see on the Oracle side is reporting wait event of "SQL*Net more data from client".The issue seems to be that Oracle is expecting more data to be sent, though all the data has shipped. This eventually finishes, though many minutes later. Is there anything that you may know is going on or a solution to this? We also do not see any network issues, but still checking it as well. This is happening now against both a production and a staging environment.

  9. #9
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    10
    Hi Colton,

    Please enable the SSIS logging and attach the SSIS log file.


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  10. #10
    colton is offline Junior Member
    Join Date
    Oct 2009
    Posts
    5
    Rep Power
    0
    Once I get a chance I will send logs. There is one thing that has sped up the process, and it was reducing the batch size back down to 100. Originally we had it at 10000, and was running fine. We tried dropping to 1000, and while it didn't seem to hang as long, it would still hang. The package now runs in about 9 minutes vs the 6 minutes originally. But much better than the 5 hours it changed to overnight with the 10000 batch size.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •