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  ----------------------------------

61 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
  21. Your Blog is appriciatable and nice. Bharat CSP is nothing but a business contributor and technology services provider to different kind of banks.

    Apply CSP
    CSP registration
    CSP provider
    bank CSP
    CSP kisok
    kisok banking

    ReplyDelete
  22. Awesome and Valuable information in this blog. Pure Digital covers a wide range of services from hosting, building your first website to Website integrations and backend systems development.

    Website Design Company in New Zealand
    Website Development Company in New Zealand
    Hosting Company New Zealand
    Website Revamp Company in New Zealand
    Web Hosting New Zealand

    ReplyDelete
  23. Thanks for giving great kind of information. Get Kiosk, CSP Mitra Banking Registration & Online Csp Application with Bank Mitra Website.

    CSP Apply
    CSP Online Application
    Apply for CSP
    Top CSP Provider in India
    Apply Online For Bank CSP
    Online Money Transfer

    ReplyDelete
  24. Great blog thanks for sharing Leaders in the branding business - Adhuntt Media is now creating a buzz among marketing circles in Chennai. Global standard content creation, SEO and Web Development are the pillars of our brand building tactics. Through smart strategies and customer analysis, we can find the perfect audience following for you right now through Facebook and Instagram marketing.
    digital marketing company in chennai

    ReplyDelete
  25. Nice blog thanks for sharing You have come to the right place. Karuna Nursery Gardens is the ideal place to begin your journey into landscape gardening. Our specialists have built some of the finest landscape garden in Chennai that too at the best price and amazing service.
    plant nursery in chennai

    ReplyDelete
  26. Excellent blog thanks for sharing Shopping for international cosmetics in Chennai? Look no further, the Pixies Beauty Shop has got your back with hundreds of international brands to choose from, that too with the best value.
    beauty Shop in Chennai

    ReplyDelete