Continuously Running Package

Continuously Running Package

• SaleBoy

  I asked this on the newsgroup a while back. I am interested as to why Donald doesn't think having a continuous package is good.
  For example having a source component that monitors a folder share would run continuously and when a file is received it either fires and event or processes the file i.e. adds rows to a buffer.
  
  The good thing about doing it that way is that you don't have to write a service and all the code is in one place.
  
  

• mike247

  Continuously running packages are probably not a good idea. You could use a trigger, and from the trigger call the SQL Agent job to execute the package as and when necessary when the flag is set.

  Donald

  
  
  

• TheJCMan

  Then why not have an agent job that kicks in every 30 seconds The problem here is that you do not really want to go to the trouble of firing up the package if nothing is there to import so I would probably insert a job step prior to this that asked if there were any rows to import. Ony if that was true would I fire the package. Allan "Darrell Davis@discussions.microsoft.com" wrote in message news:ebb01022-f6fa-4aba-8ee3-40117cac3ee0@discussions.microsoft.com: > [quote user="Donald Farmer"] > > Continuously running packages are probably not a good idea. You could > use a trigger, and from the trigger call the SQL Agent job to execute > the package as and when necessary when the flag is set. > > Donald > > [/quote] > > I can't use a trigger because I don't have control over the source > system where the control table is. I have to query the control table > every 30 seconds. > > -Darrell
  

• Cputans

  The second job won't run and will only run the next time it is scheduled to run
  
  

• Paul Learning

  Donald Farmer wrote:

  Continuously running packages are probably not a good idea. You could use a trigger, and from the trigger call the SQL Agent job to execute the package as and when necessary when the flag is set. Donald

  
  
  I can't use a trigger because I don't have control over the source system where the control table is.  I have to query the control table every 30 seconds.
  
  -Darrell
  

• Amit Grover

  Michael Entin - MSFT wrote:

  You can have a package that runs continuously: Create an Loop task, use some variable (initially true) as loop continuation condition. Inside the loop: add the package logic, a script task that will do the sleep for required time, and some task to set the variable that is used for loop condition (e.g. script task or SQL task) - depending on how you want to stop the package. .....

  Is there any drawback of this approach
  
  
  

• Yogita Manghnani

  Again, thank you SimonS. You have been most helpfull and patient.
  

• exulted

  Thank you for your speedy response SimonS. So if I understand the "preferred" process (again, please excuse the simplicity of my question)...SQL Agent is set to run every 5 minutes (or whatever time frame). At 8AM the job starts, calling my SSIS package which processes any files stored in the specified folder. At 8:05AM, when the SQL Agent is supposed to run again, the SSIS package is still processing files in the folder so the 8:05AM job does not run. The SSIS package, and the 8AM SQL Agent job, complete at 8:09AM. At 8:10AM the next scheduled firing of the SQL Agent job occurs. Am I correct, or does the 8:05AM job simply wait until the 8AM job completes and then IT runs Thanks again for helping me with a task I've been thrown into without the proper training.
  

• Randy Miller

  You are very open to small issues like meory leaks with this approach. I prefer to continuously running a package using a schedule, i.e. every few minutes. You could combine the 2 which is a common appraoch with Service broker.
  
  

• Carlos Valenzuela

  No its not possible for SQL Agent to have the same job running twice at the same time.
  
  

• RACHA

  You can have a package that runs continuously:

  Create an Loop task, use some variable (initially true) as loop continuation condition. Inside the loop:

  1. add the package logic,
  2. a script task that will do the sleep for required time, and
  3. some task to set the variable that is used for loop condition (e.g. script task or SQL task) - depending on how you want to stop the package.

  You can also stop such package from Management Studio - connect to local SSIS service, find the package under running packages, right-click, select Stop. You can omit the third step if you are ok with this method.

  But as others suggested, you may first try to use other approaches.

  
  
  

• username

  SimonSa wrote:

  I asked this on the newsgroup a while back. I am interested as to why Donald doesn't think having a continuous package is good. For example having a source component that monitors a folder share would run continuously and when a file is received it either fires and event or processes the file i.e. adds rows to a buffer. The good thing about doing it that way is that you don't have to write a service and all the code is in one place.

  Simon - you are describing two very different solutions here:

  1) a task that fires event that is configured to run some work
  Note that if the folder is local you can use WMI task for this. This is a reasonable approach, although pulling for data means you are spending CPU cycles every N seconds - usually not good solution. Doing anything only when needed (e.g. WMI task or SQL trigger) is usually better. Also I'm not sure if making this code a task that fires an event saves you any coding compared to simply directly running the package from the same code.

  2) data source that continuosly adds rows to a buffer
  This is more tricky. First, if you have asynchronous transform like sort in the data flow you will get no output until the source tells that it had read all the data. The data will be accumulated by sort transform in this case, waiting for source to stop. Probably not what you want.

  If you don't have any asynchronous transforms, the things are a bit better, but one may still find some surprises. E.g. user might expect that once the source component processed a file, all rows from this file reach destination in some finite amount of time. But this is not always true - the data flow processes full buffers. So if source file did not fill the buffer exactly, part of it will not be sent to transforms until new data from the next file fills this buffer.

  
  
  

• Shekar Gudi

  Please excuse me if this is very basic...can a SQL Agent Job be fired off while it is already running For instance, I need to poll a folder for new files and take action if the files are found. It is possible to have many files delivered within a short amount of time. A For Loop in my package runs until all the files in the folder are processed...so what happens if the loop takes longer than the "five minute interval" to complete Will a second instance of the job start, trying to process the same files that the first instance is processing I haven't had this overlap yet but a new process being put in place will certainly create this very scenario for me and I'm not sure how to handle it. Thanks for any help.
  

• Menko

  The two scenarios were intentionally different. The first is the most likely situation i.e. using the notifications mechanisms in .net for the file system and even the SQL Dependency. The reasoning is that the applications are different, i.e. monitoring an ftp folder or auditing changes to a database. You have no control over the applications that instigate the process. For this reason Having one package that is continually running makes sense to me, I would prefer a package to be running all the time (whilst not consuming too many cycles) rather than one that is launched every x seconds (the launching of a package might take x seconds). That way you don't have the overhead of laucnhing a package and its easy to see if the package is running.
  
  I appreciate the asynchronous issue and I would only want this to process complete chunks of work, i.e. not leave anything incomplete for a subsequent run.
  
  The impression I got from Donald was that having a package running this way will have problems. Could understand if SSIS had a memory leak, which I am sure hasn't ;) (anymore anyway).
  
  
• Continuously Running Package