Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you want to execute a package as a regularly scheduled occurrence there is a part of SQL Server that can do that for you but it's a part that's actually turned off when you first install it. In fact if I open up another instance of Management Studio, I'm going to connect to the database engine and you may have seen this before, that there is an entry here that says SQL Server Agent. You may have seen it in Management Studio or if you had opened up the Configuration Manager you would also see it there.
SQL Server Agent is stopped by default and its Start Mode is Manual, which means when the machine is restarted it will not start itself. It's again because of what Microsoft tend to do these days, which is they ship products with only the necessary part of them turned on. Now SQL Server Agent isn't absolutely necessary so it's off by default and you have to turn it on if you want to use it. I can just turn it on quite easily from SQL Server Configuration Manager, just hit the little Play button and we'll turn it on, and if I wanted to I could also right-click, select the Properties of it, and then make sure that it actually changes the Start Mode to Automatic.
I am not going to do that right now because I don't want to make it Automatic but I could do. What is SQL Server Agent? Well it is a supporting service that allows us to have jobs that are scheduled in the background. It allows us to have an asynchronous kind of queuing mechanism. It can control tasks to be repeatedly happening in the database. You just have to know it's there in order to turn it on. Once it is on, then what? Well I am going to expand this and it says, well we've got Jobs, we've got Alerts, and we've got Operators, Proxies, and Error Logs.
SQL Server Agent works with the idea of a Job, I am going to make a new one. I right-click and say a New Job. I give it a name and then I say what it is. Let's say this is a job that I want to run every week or so that converts new customers that are being loaded into some kind of text file. Convert Customers. Yup, I'm the owner. It doesn't really fit into a Database Engine Tuning Advisor or Database Maintenance. I am just going to leave it as Uncategorized right now, and then we step through the steps.
What do we do, and the schedules, when do we do it? So the step first, nothing is happening right now. I'll say I want to create a new step, and the step name will be Do The Convert and it's asking is it a T-SQL script or Replication Merge, ActiveX Script or is it a SQL Server Integration Services Package? And of course that's what it is, exactly. What are we going to run as the SQL Server Agent Service Account? Yes, absolutely that should do the trick with this one. It doesn't really need any kind of significant account there.
It's going to ask where is the package? It's in SQL Server? Which server? This one, the dot? We are going to use Windows Authentication and then we'll drop down and select the package. Would have been useful if I'd given it a more interesting name but that will do the trick. We click OK. I don't need to change any of the other setups here. That will do just fine and I click OK. We've got the step selected. Now I need to define the schedule. I click the Now button and it asks, okay, what's the name for this schedule is? Let's call it on Monday Morning.
Just so it's descriptive when we read this later. It is a reoccurring schedule. It will happen weekly, on a Monday Morning let's say at 5 a.m. and there's no end date. Pretty much like setting up or occurring appointment in Outlook. We have some more choices about alerts and actions to perform when the job completes, I want to get an e-mail, when the Job succeeds, when the Job fails, this does require you to have database mail turned on, which we don't have at this particular moment, but you could do that, and that's pretty much it. Going to click OK.
We have the job, SQL Server Agent is started, we would want to make sure that it was set up to start automatically if the machine ever go restarted but that's the general process for doing it. And simply by doing this we can start to build more complex, more automated situations. Of course SQL Server Integration packages are the only jobs that you can schedule in SQL Server Agent. As you saw there is the ability even to just write raw T-SQL so you can have database consistency checks, you can automate the rebuilding or the re-organizing of indexes, and you really can start to build a more automated, more controlled database structure.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91122 Viewers
80 Video lessons · 138161 Viewers
59 Video lessons · 56978 Viewers
52 Video lessons · 70612 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.