Ever since SQL Server Integration Services (SSIS) was introduced in Microsoft’s SQL Server 2005, ultimately replacing DTS (Data Transformation Services), I have been a huge fan and have implemented it in a variety of projects (another discussing topic all together). However, one thing I have noticed scrounging around the net is no one has instructions on how to schedule an SSIS package from start to end. While many of us know how to create new packages and update existing schedules on the server, I had personally forgot how to get everything started from a clean install of SQL Server.
So for anyone that is new to SSIS, here’s a quick description of what you need to do to get your SSIS packages scheduled and running on your SQL Server.
First and foremost, you’ll need to make sure you have your package and any configuration files copied to the server. I suggest having one configuration per package (but to each his own). On my server, I have created a couple directories, C:SSISPackages and C:SSISConfigs, on my server for this purpose.
Next, make sure you have both “SQL Server Integration Services 10.0″ and “SQL Server Agent (MSSQLSERVER)” services running on your server. Not only have I seen this mistake from a couple of newbies, but also from some more experienced guys.
So let’s start. Open up Microsoft SQL Server Management Studio and connect to your server.
Expand SQL Server Agent. Right click Jobs and select New Job. The following window will appear.
Enter a Name of the job. The rest on this is really up to the individual administrator.
Select Steps from the side navigation. Click the Add New button to create a new step.
Enter the Step Name of your choosing. You may want to execute another package after this one. In that case, then you would just create another step.
Select SQL Server integration Services Package from the Type list. Make sure the step runs as a SQL Server Agent Service Account.
The next step is very important and absolutely destroyed an afternoon of another administrator I know. Select File system from the Package source. The other option is to deploy the package to the server and select it from the server. I have never really had success in this to be honest. Which normally means I did it wrong…many many times.
Click the ellipse (…) button to add a package file. Navigate to your package location on the server. Click OK.
Now, select the Configurations tab. Here is where you can add any configuration files you might have for the selected package.
There are many other options here for each job. But this is a blog and don’t want to be here all night writing. So click OK.
So, have added a step. As I stated before, if you’d like to create additional steps (adding more packages to the job), here’s your chance. If not, then it’s time to schedule your job.
Click Schedules from the side navigation. Click the New button.
This screen really needs no explanation. If you have done a backup of any kind in your career, then here you go. If you haven’t, then I commend you on getting this far.
Click OK to save the job. We will have to have another article for the Alert andNotifications sections. They are so important to many of us, they really do deserve their own articles. Enjoy!