Database Administrators often get requests from development teams to deploy and schedule newly created SQL Server Integration Services (SSIS) packages across different environments. In this article you will see the steps that you need to follow in order to schedule an SSIS package as a SQL Server Agent Job using SQL Server Management Studio (SSMS). SQL Server Agent Jobs can be used to schedule and execute SSIS Packages which are stored in SQL Server, or in File System, or in the SSIS Package store. When we say a SSIS Package is stored in the SSIS Package store, it is basically stored as a file within SQL Server’s “C:Program FilesMicrosoft SQL Server100DTSPackages” folder. In this article you will see how to schedule a SSIS package which we have created in my previous article titled “How to Create a Comma Separated Delimited Text File Using SQL Server 2008”. This package creates a comma separated delimited text file for all the data which is available in “Sales.Customer” table of AdventureWorks database.
Prerequisites for Scheduling SSIS Packages
As a prerequisite the SQL Server Agent Service should be running once the package is scheduled to run as a SQL Server Agent Job. You can start the SQL Server Agent Services in numerous ways like:-
- Starting SQL Server Agent Service from Command Line
- Starting SQL Server Agent Service from Services.MSC console
- Starting SQL Server Agent Service using SQL Server Configuration Manager
- Starting SQL Server Agent Service using SQL Server Management Studio (SSMS)
Starting SQL Server Agent Service using SQL Server Management Studio
1. Connect to a SQL Server 2008 Instance using SQL Server Management Studio.
2. Right click SQL Server Agent Node and select Start from the popup window.
3. Once you click on Start from the popup window, it will open up a dialog box as shown in the snippet below where you need to click on Yes to start the SQL Server Agent Service.
In this article you will be creating a SQL Server Agent Job using SQL Server Management Studio. The job will schedule SalesCustomer.dtsx package to run once a day at 12 AM. This package basically creates a comma separated delimited text file for all the data which is available in Sales.Customer table of AdventureWorks database. To know how to create a SalesCustomer.dtsx package you can refer to my previous article titled “How to Create a Comma Separated Delimited Text File Using SQL Server 2008”.
Creating a New SQL Server Agent Job for Scheduling an SSIS Package
1. In order to create a new SQL Server Agent Job to schedule the SalesCustomer.dtsx package; you need to expand SQL Server Agent node and then right click the Jobs node. From the popup windows select New Job…. as shown in the snippet below.
2. In New Job window you can provide the details such as Name, Owner, Description etc as shown in the below snippet. However, you also need to make sure that the Enabled checkbox is selected. This basically means that once the job is created successful it remains active so that at the specified schedule time the job runs automatically. The prerequisite for jobs to run automatically is that the job should be scheduled and the SQL Server Agent Service is running.
3. Select the Steps page from the “Select a page” panel on the left. And finally click the New… button to create a new job step as shown in the snippet below.
