Scheduling the DTS Package

A saved DTS package can be scheduled to execute at a specific time. A scheduled DTS package is executed by SQL Server Agent as a job. The DTS package can be scheduled to execute once, or at recurring intervals. Examples of intervals include:

• Daily at midnight.
• Weekly on Sunday at 6 A.M.
• The first or last day of the month.

A scheduled DTS package is executed by the SQL Server Agent service as a job. Because the SQL Server Agent service controls the underlying automation for scheduling, it must be running for any scheduled packages to execute. You can schedule for execution a package created in DTS Designer:

• In SQL Server Enterprise Manager, by selecting a DTS package saved to the Microsoft® SQL Server™ msdb database or Microsoft Repository and using the Schedule Package option.
• Using SQL Server Enterprise Manager, by creating a SQL Server job that executes the DTS package using the dtsrun command prompt utility. You should use this option if you need to schedule a package saved as a data transformation services (.dts) file, or if you want to schedule a package for execution on a server other than the local server.

Using the Schedule Package Option

The Schedule Package option is the easiest way to schedule for execution; however, the package must have been saved to either the SQL Server™ msdb database or Repository, and it must exist on the local server.

To schedule a DTS package using the Schedule Package option

Using SQLServerAgent to Schedule a Package

You can also use SQLServerAgent to schedule any package created within DTS Designer (including Data Transformation Services, or .dts files), within the DTS Import and DTS Export wizards, or as an external program.

To schedule a DTS package using the SQLServerAgent service

SQL Server Agent Permissions

Scheduled packages are executed by the SQL Server Agent process, which uses permissions specified during server registration. The permissions, path, and share drive letters of the SQL Server Agent process are likely to be different than those of the client process that defined and saved the DTS package. The SQL Server Agent process must have permissions to open the package, and the package location specified in the job definition must be valid from the SQL Server Agent process:

• For packages saved in a DTS file, the file location in the job definition must be valid from the SQL Server Agent process. The SQL Server Agent process must also have any permissions needed to open the file.
• For packages saved using integrated security in either SQL Server storage, the SQL Server Agent process must be running with a Windows operating system account that has permissions to access the DTS-related or Repository system tables in the msdb database.

See Also