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

87 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
    Replies
    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important. Project Center in Chennai It’s what organizations do with the data that matters. Big data can be analyzed for insights that lead to better decisions and strategic business moves.

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.
      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai


      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
    2. You have posted the blogs are really fantastic and informative.erp software dubai

      Delete
  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. hello sir,
    thanks for giving that type of information. Epoxy Grout manufacturer in delhi

    ReplyDelete
  10. 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
  11. 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
  12. 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

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

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

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

    ReplyDelete
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. Very useful blog thanks for sharing IndPac India the German technology Packaging and sealing machines in India is the leading manufacturer and exporter of Packing Machines in India.

    ReplyDelete
  29. I like this post,And I guess that they having fun to read this post,they shall take a good site to make a information,thanks for sharing it to me. job posting

    ReplyDelete
  30. I enjoy reading an article that can make people think dev. Also, thanks for allowing for me to comment!

    ReplyDelete
  31. Interesting and amazing how your post is! It Is Useful and helpful for me That I like it very much, and I am looking forward to Hearing from your next..
    Jobs in Dubai

    ReplyDelete
  32. I definitely enjoying every little bit of it. It is a great website and nice share. I want to thank you. Good job! You guys do a great blog, and have some great contents. Keep up the good work. seo

    ReplyDelete
  33. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way. T hank you so much.

    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery


    ReplyDelete
  34. IEEE Cloud computing DOamin is a general term for anything that involves delivering hosted services over the Internet. cloud computing projects The cloud projects for cse is a metaphor for a global network of remote servers which operates as a single ecosystem, commonly associated with the Internet. IEEE FInal Year Networking Projects for CSE Domains Networking Projects cloud computing is the delivery of computing projects services—including servers, storage, databases, networking projects, software, analytics, and intelligence


    JavaScript Training in Chennai


    JavaScript Training in Chennai

    ReplyDelete
  35. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  36. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  37. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
    Lucknow Chikankari Kurti

    ReplyDelete
  38. Thanks Admin For sharing this massive info with us. it seems you have put more effort to write this blog , I gained more knowledge from your blog. Keep Doing..Chikan Suits shops in Lucknow

    ReplyDelete
  39. Your Blog is really awesome to read and useful for everyone,thanks for sharing such a wonderful article.keep updating..
    AWS Certification Exam Center in Chennai | AWS Exam Center in Velachery | Best AWS Exams in Chennai

    ReplyDelete