Post reply

SSIS - File System task maxing out memory

  • September 16, 2011 at 7:56 am

    #248097

    Hi

    I am working on a SSIS package to archive documents to disk from cases that haven't been touched in a couple of years. The aim of this is to decrease the size of our database to a more manageable size. Overall around a million documents are to be archive. I have written the first few steps of this process namely:

    1)Identify the documents to archive & store in a table

    2)identify the directories to create & store in a table

    3)use a Loop & file system to create each of the directories 1 at a time

    4)use a loop task to export 1000 documents at a time to the created directories.

    When I run a test on 50,000 documents and 10,000 directories I have no problems. The whole process completes sucessfully in about 22 minutes. However, when I run another test using 103,000 documents and 29,000 directories its an entirely different matter. After around 30 minutes I can see that the directories have all been created, however the file system loop task is still running 2 hours later. I've also noticed that the physical memory on the server has been completely maxed out where as it wasn't when the directories were actually being created.

    Could someone point me in the right direction as to what to look at to identify what is going on, or alternatively a better way to create these directories rather than one at a time.

    Thanks

    Sarah

  • September 20, 2011 at 8:57 am

    #1383895

    Has anyone got any suggestions?

    pretty please

  • September 20, 2011 at 9:00 am

    #1383897

    Hi Sarah,

    Saw this when you first posted and didnt respond because I'm not sure, but my thought is that the process spawned at the OS level is constantly grabbing more and more memory... not sure how I would correct that, but maybe try to treat it like a runaway query type situtation... work in smaller batches, stopping the process as it goes. do 10000 at a time or something like that.

  • September 20, 2011 at 9:08 am

    #1383909

    Thanks for replying, I think you are right , small batches may well be the way forward.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply