none
How would you handle this situation?

    Question

  • Here is the basic situation.

     

    (1) I have 2 packages that run independently of eachother.

    (2) Both are scheduled in SQLServer Agent Jobs as separate jobs.

    (3) Job A is scheduled to run every 12 hours and Job B is scheduled to run every 10 minutes.

    (4) However, I want to prevent Job B from running if Job A happens to be running.

    (5) It is unknown exactly how long Job A will take to finish so I can't schedule Job B around it.

     

    The way I wanted to approach this situation is as follows.

     

    Within Job A's package, create a "marker" file when the package starts and delete it when the package finishes. So the existence of this marker file will tell Job B's package if it should run or not.

     

    The concept is simple, but I'm not sure how to implement this.

     

    For example, to create the marker file, I would use a File System Task, but I don't see an option in there to "create file". (However, I do see an option for "delete file".) Also, what task would I use in Job B's package to check if the marker file exists.

     

    Lastly, If you have better approach, I would like to hear about it.

     

    Thank you.

     

     

     

     

     

    Tuesday, April 29, 2008 8:13 PM

Answers

  • To check if a file exists, you need to use a Script Task with the System.IO.File.Exists() method, as shown here:

    http://agilebi.com/cs/blogs/jwelch/archive/2007/10/31/checking-for-the-existence-of-a-file.aspx

     

    To create a file, you can use System.IO.File.Create() from a Script Task.

     

    Tuesday, April 29, 2008 8:32 PM
  • I think using a 'touch' file or an execution status table is perfectly valid in this scenario; which one is better? that may be a matter of preferences. Personally I prefer the table based solution, but that may be because I feel more comfortable writing queries than custom code.

    Tuesday, April 29, 2008 9:42 PM
  •  fftw_ayi wrote:

    Thank you for that reference.

     

    Getting back to the original question, do you think the use of a "marker" file is the best approach? I guess I can also have a dummy table and insert a line or do something like that as an indicator.

     

    Besides using some kind of an indicator as described above, is there a system variable that I can try to utilize? Perhaps something like IsRunning(PackageName) that returns a boolean?

     

     

    A marker file or a record in a table would work fine. If you want to check for running packages programmatically, you can use the SSIS API. The Application class has a GetRunningPackages method.

    Wednesday, April 30, 2008 2:40 AM
  •  jwelch wrote:
     fftw_ayi wrote:

    Thank you for that reference.

     

    Getting back to the original question, do you think the use of a "marker" file is the best approach? I guess I can also have a dummy table and insert a line or do something like that as an indicator.

     

    Besides using some kind of an indicator as described above, is there a system variable that I can try to utilize? Perhaps something like IsRunning(PackageName) that returns a boolean?

     

     

    A marker file or a record in a table would work fine. If you want to check for running packages programmatically, you can use the SSIS API. The Application class has a GetRunningPackages method.

     

    I have no idae what SSIS API is. I think I'll stick with the file or table solution. Thanks for the inputs.

    Wednesday, April 30, 2008 12:59 PM

All replies

  • To check if a file exists, you need to use a Script Task with the System.IO.File.Exists() method, as shown here:

    http://agilebi.com/cs/blogs/jwelch/archive/2007/10/31/checking-for-the-existence-of-a-file.aspx

     

    To create a file, you can use System.IO.File.Create() from a Script Task.

     

    Tuesday, April 29, 2008 8:32 PM
  • I'm relatively new to SSIS and I noticed that Script Task comes up a lot to do custom tasks. What language is Script Task using? And what would be the best way to learn this?

    Tuesday, April 29, 2008 8:38 PM
  • It uses VB.NET. There is the "The Rational Guide to Extending SSIS 2005 with Script", which is a quick reference that I found useful.

     

    For general VB/.NET questions, I like the O'Reilly books.

     

    Tuesday, April 29, 2008 8:47 PM
  • Thank you for that reference.

     

    Getting back to the original question, do you think the use of a "marker" file is the best approach? I guess I can also have a dummy table and insert a line or do something like that as an indicator.

     

    Besides using some kind of an indicator as described above, is there a system variable that I can try to utilize? Perhaps something like IsRunning(PackageName) that returns a boolean?

     

    Tuesday, April 29, 2008 8:53 PM
  • I think using a 'touch' file or an execution status table is perfectly valid in this scenario; which one is better? that may be a matter of preferences. Personally I prefer the table based solution, but that may be because I feel more comfortable writing queries than custom code.

    Tuesday, April 29, 2008 9:42 PM
  •  fftw_ayi wrote:

    Thank you for that reference.

     

    Getting back to the original question, do you think the use of a "marker" file is the best approach? I guess I can also have a dummy table and insert a line or do something like that as an indicator.

     

    Besides using some kind of an indicator as described above, is there a system variable that I can try to utilize? Perhaps something like IsRunning(PackageName) that returns a boolean?

     

     

    A marker file or a record in a table would work fine. If you want to check for running packages programmatically, you can use the SSIS API. The Application class has a GetRunningPackages method.

    Wednesday, April 30, 2008 2:40 AM
  •  jwelch wrote:
     fftw_ayi wrote:

    Thank you for that reference.

     

    Getting back to the original question, do you think the use of a "marker" file is the best approach? I guess I can also have a dummy table and insert a line or do something like that as an indicator.

     

    Besides using some kind of an indicator as described above, is there a system variable that I can try to utilize? Perhaps something like IsRunning(PackageName) that returns a boolean?

     

     

    A marker file or a record in a table would work fine. If you want to check for running packages programmatically, you can use the SSIS API. The Application class has a GetRunningPackages method.

     

    I have no idae what SSIS API is. I think I'll stick with the file or table solution. Thanks for the inputs.

    Wednesday, April 30, 2008 12:59 PM