Tuesday, July 8, 2014

Execution of SQL Agent Job using Seed File

Purpose of the Article

This article is devised to give a way of running the jobs on demand irrespective of scheduled process. This is very useful in such a case when there is no fixed time for data processing. Incoming data can come anytime and at the same time, system should run the job for data processing. To achieve this, many times we schedule our jobs to run at every half an hour/ 10 mins/ 15 mins etc which in turn wastage of system resources. We can control unnecessary execution of job by using Seed file that tells to Job whether it has to start execution or not.
What is Seed File?
Seed file can be any type of blank file (.txt,csv,.xls) that can be used as input parameter to start the execution of any process. For quintessence-

IF Seed file is available
BEGIN
            START THE PROCESS
END
ELSE IF Seed file is not available
BEGIN
BYPASS THE PROCESS
END

How to control the execution of jobs using Seed file?
1.      To control the execution of jobs, we need an additional step that should be the first step of the job which should check whether seed file is available in the source folder or not.
2.      If seed file is available then job should go to next step and execute as expected and at last step, Job should delete the seed file.
3.      If seed file is not available in the source folder then Job should by pass the steps and should quit with reporting success.
Steps to control the execution of Jobs using seed file:
1.  Create first step of the job for checking whether file is exists or not. For checking file existence and Deletion, we are using XP_FILEEXIST and XP_CMDSHELL extended stored procedures. Below is the code which checks whether file is exists or not.

If file exists, we are simply doing select 1
If file does not exists then we are raising the error with message- ‘Seed file is not available’

DECLARE @Flag INT
DECLARE @FilePath VARCHAR(500)
DECLARE @DelFilePath NVARCHAR(500)
SELECT @FilePath = '\\IND-SQL-01\Processing\Start_Processing.txt'


EXEC MASTER..XP_FILEEXIST @FilePath, @Flag OUT
IF @Flag = 1
BEGIN
       SELECT 1
END
ELSE
BEGIN
   RAISERROR('Seed File is not available',16,1)

END
GO

Fig showing the first step of the job for checking file existence

2.      Create one more step that should be the last step of the job that should delete the seed file if exists to clean the source folder for next execution. Below is the code for deleting the file:




DECLARE @Flag INT
DECLARE @FilePath VARCHAR(500)
DECLARE @DelFilePath NVARCHAR(500)
SELECT @FilePath = '\\IND-SQL-01\Processing\Start_Processing.txt'
SELECT @DelFilePath = 'DEL' + SPACE(1) + @FilePath


EXEC MASTER..XP_FILEEXIST @FilePath, @Flag OUT
IF @Flag = 1
BEGIN
   EXEC MASTER..XP_CMDSHELL @DelFilePath

END


                 Fig showing the last step of the Job for cleaning the seed file

3.      Go to Step first then Edit then Advance. Set the below setting
a.      On Success : Go to Next Step
b.      On Failure:  Go to Last Step.

4.      Job Steps:

In this way, we can bypass the processing steps if seed file is not available and we can save unnecessary utilization of system resources required for execution of the job.


Conclusion
By using the above mentioned steps, we can implement on demand job execution functionality.


----------------------------   Author -  Vishal Jharwade  ----------------------------------

48 comments:

  1. This blog is having a wonderful talk. The technology are discussed and provide a great knowledge toall. This helps to learn more details about technology. All this details are important for this technology. Thank you for this blog.
    Hadoop Training in Chennai

    ReplyDelete
  2. Hi Vishal Jharwade, This is just to let you know that how good you are writing. I read all your articles, all are nicely explained & having information which we can never get from anywhere except sqlcircuit.com. I love all your articles & request to keep writing the post.

    ReplyDelete
  3. Superb explanation & it's too clear to understand the concept as well,thank you for your sharing

    Web Development Company In India

    ReplyDelete
  4. Thanks for posting this useful article,Keep updating such a Great information of Blog..

    Java Training Institute in Chennai | Java Training in Chennai | Online Training Institute in Chennai

    ReplyDelete
  5. Really an amazing post..! By reading your blog post i gained more information.
    SEO MOBILE MARKETING

    ReplyDelete
  6. I read this article. I think You put a lot of effort to create this article. I appreciate your work.
    thesis Writing Service

    ReplyDelete

  7. Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.
    digital marketing company in delhi

    ReplyDelete
  8. You have explained very exacty what has done very simply in this blog.
    contact us:Livewire Velachery

    ReplyDelete
  9. led lawn lights in delhi
    Thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog, nice work keep it up thanks for sharing the knowledge.

    ReplyDelete
  10. Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    Best Devops training in sholinganallur
    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram

    ReplyDelete
  11. Played on BGAOC with big wins? NOT? Come to us as soon as possible and win with us. perfect all slots casino Come get your chance to win.

    ReplyDelete

  12. بسم الله الرحمن الرحيم نحن فى شركة الكمال تقوم بافضل انواع التنظيف العام وتنظيف الفلل بافضل

    انواع العالميه التى تحافظ على السيراميك
    شركة تنظيف منازل بحائل
    شركة تنظيف بالطائف
    شركة تنظيف بجازان
    شركة تنظيف بحائل
    شركة تنظيف مجالس وكنب بحائل
    ونحن فى خدماتكم اربعه وعشرون ساعه وكل هذا بافضل الاسعار واقل التكلفة

    ReplyDelete
  13. Качественная лед лента разных цветов, герметичные и нет, я обычно беру в Экодио

    ReplyDelete
  14. Your Blog is really awesome with useful and helpful content for us.Thanks for sharing ..keep updating more information.

    AWS Training Institute in Chennai | AWS Certification Training in Velachery | AWS Exam Center in Chennai | AWS Online Exams in Chennai

    ReplyDelete
  15. Very impressive and interesting blog, it was so good to read and useful to improve my knowledge as updated one,keep updating..This Concepts is very nice Thanks for sharing.

    AWS Training Institute in Chennai | AWS Certification Training in Velachery | AWS Exam Center in Chennai | AWS Online Exams in Chennai

    ReplyDelete
  16. Very impressive and interesting blog, this is the best place to get wonderful information thanks much for sharing here...
    Linux Training Institute in Chennai | Linux Training Center in Chennai | Online Training in Chennai | Linux Certification in Chennai

    ReplyDelete
  17. Your blog is really useful for me. Thanks for sharing this useful blog..thanks for your knwoledge share ... superb article ... searching for this content.for so long.
    AWS Training Institute in Chennai | AWS Certification Training in Velachery | AWS Exam Center in Chennai | AWS Online Exams in Chennai

    ReplyDelete
  18. Your blog is very useful for me,thanks for sharing such a wonderful post with useful information.keep updating..
    Python Training Center in Chennai | Python Certification Training in Chennai

    ReplyDelete
  19. Your Blog is really awesome with useful and helpful content for us. Thanks for sharing. Keep updating more information.
    Tally Training Institute in Chennai | Tally Training in Velachery

    ReplyDelete
  20. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    Java Training in Chennai
    Java Training in Coimbatore
    Java Training in Bangalore

    ReplyDelete