Using Data Transformation Services: Package Execution Using SQL Server Agent, Introduction - Part B: Scheduling the Package: Create a SQL Job
(Page 4 of 4 )
Saved DTS packages can be run by several means, which include invoking the dtsrunui from the command prompt. But in this section, a method using the SQL Server Agent is described. In order to access the SQL Server Agent, expand the management node of the SQL Server. This gives access to the SQL Server Agent.
This comprises the collections, Alerts, Operators, and Jobs. In order to keep the discussion simple only the Jobs will be discussed. But in a production environment all of them are necessary for successful management of the database. For the purposes of this tutorial a new job is created, either from right clicking Jobs as shown, or from the menu item (the icon is similar to the one for job, but shows the pop-up text 'New Job').
Configuring and Scheduling the Job
This creates a new job on the SQL Server which must be configured for the job at hand. It comes with several tabs, General, Steps, Schedules, and Notifications as shown. The General tab is configured with the required information, such as the name for the job, a description of its purpose, whether it is enabled or not, whether it runs on one server or several, and so on.
In the second tab, Steps, it is necessary to describe the various steps in this job. These steps are created new, but can be inserted, deleted, or edited. If it is already created (true in this case), its property can be inferred by editing it as shown.
The name of the step is Only Step. In this step an operating system command to the SQL Server is given, which runs the package for a server with Windows Integrated authentication (switch /e). The switch /N is for the package name and follows it, as shown above.
In the Schedules tab, the actual scheduling is carried out. It has several fields that need configuring. In this tutorial, it is run just once at a predetermined time as shown. However, depending on the requirements, scheduling can be flexibly implemented.
This leaves this tab in the following state. If need be, this can be edited, or changed.
In the next tab, Notifications, messages are sent to operators (email, etc). The next picture shows the Notifications window. The job, if it succeeds, writes to the Windows event log.
Save the job
This completes the configuration of the job. When it is saved, a related icon appears in the listing of jobs in the Enterprise Manager at the Job's node as shown.
By right clicking the line item of the job in the listing, several of the job related tasks, such as start and stop, access job history, and so on, can be carried out. The next picture shows the job history for this tutorial's job configured above.
Summary Only the simplest of the job scheduling, with just one step, carrying out a one time execution, was shown. Not only the jobs can have many steps, but the SQL Server agent can be started in various ways, automatically, whenever the CPU is idle, one time as in this tutorial, or recurring according to a set pattern.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 8 
