December 8, 2016 at 8:25 am
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...
December 8, 2016 at 8:45 am
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.
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]
December 8, 2016 at 9:22 am
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.
December 8, 2016 at 10:16 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply