Long running SSIS packages gradually use up memory

  • kyagi.jo

    SSCrazy

    Points: 2364

    Hi,

    I am currently using a SSIS WMI task to watch a folder, which runs in a for-each loop, until a file is dropped into the folder. Looking at memory usage, it seems like the longer the package runs, the more memory that the package takes, even when the package doesn't look to be doing much, other than checking over and over whether the folder has a new file.

    I have created the package so that it will stop running after a file does finally appear, resetting its memory usage, but while it is sitting in memory, I don't want it to keep increasing in size, until a file can arrive, as some of these packages may process files that arrive once every few weeks.

    Can anyone suggest any way I can "lighten" the footprint of the package, while it sits in memory for a long time? The memory usage looks to never go down, only up. There are two processes in my task manager, one of which is "SQL Server Integration Services Execute Package Utility" and the other is "SSIS Execution Package". Has anyone else had experience with packages that don't really complete, and will just keep constantly running?

    Edit: Oops was meant to put this in the SSIS forum; the server I am deploying to is SQL server 2016 though. Don't see an option to change it now, or delete my post...

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    I ran into a similar problem before importing thousands of files from an archive. I ended up setting up the package to only run for a few hours at a time and scheduling the package to run every 5 minutes if it wasn't already running.

    Each time the package stopped, the memory it was using was released.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • kyagi.jo

    SSCrazy

    Points: 2364

    Hmm it's a shame it can't indefinitely run. I think in that case I will time out the package after a day. I'm also toying with the idea of whether I I could improve things by executing another package with most of my business logic, so that WMI event package is a lot more lightweight, until it actually has to do some work, whereupon it would load another package for it.

  • frederico_fonseca

    SSChampion

    Points: 14331

    What I normally do on these cases is to have either a C# or a Powershell script that monitors the folder and once a new file arrives kicks off the load process (being it a SSIS package or a C# program).

    Processing done in parallel, normally with a max of 8-10 packages running in parallel if that many files arrive.

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

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