Executing a Deployed Package

You have some choices about how you can execute a package, none of which is very complicated. The easiest method is to locate the package in SQL Server Management Studio Integration Services, right-click the package, and choose Run Package from the menu. This might be simple, but it's certainly not very convenient if you want the package to run every morning at 2 A.M.-especially when you're on vacation in Hawaii!

Fortunately, a package can also be scheduled to run automatically by the server. Typically, you do this by using the SQL Server Agent with a simple command-line script. Using the Agent, the job can be scheduled to run at set intervals. Alerts can be set up to send e-mail, page, or log entries upon success or failure.

Regardless of whether you run the package manually or schedule it to run automatically, you will actually use one of two utility programs:

• DTExecUI Opens Execute Package Utility in a Windows dialog box

• DTExec Runs the command-line package execution utility

Running DTExecUI from a shortcut or the command line opens the Windows Execute Package Utility dialog box, as shown in Figure 10-1. This will allow you to specify configurations, optional settings, logging, and execution options on various tabbed property pages in the dialog box. Choosing Run Package within SSMS launches this utility application.

Figure 10-1: General settings using the Execute Package Utility.

Running DTExec from the command line will perform the same operations, using a single command-line string. Although the DTExecUI utility may be dressier, the command-line utility is more versatile and might be preferable in many situations. Using this utility is a convenient way to run a package from a batch or command file.

There is an easy method to assemble the command-line string used to run this utility with all of the necessary options. Run Execute Package Utility (DTExecUI), and then choose all of the appropriate options and execution parameters. On the last tab of this dialog box, labeled Command Line, as shown in Figure 10-2, a text box displays all of the execution options as a command string. Simply copy and paste this string into the command line or to a batch file in Notepad after the text DTExec. It's that easy!

Figure 10-2: Command-line settings using the Execute Package Utility