You can execute packages in the following ways:
Clicking Execute in DTS Designer on the toolbar or the Package menu
Right-clicking a package in SQL Server Enterprise Manager and clicking Execute Package
Right-clicking a package in SQL Server Enterprise Manager and clicking Schedule Package to create a scheduled SQL Server Agent job
Using the DTSRunUI utility
Using the DTSRun command prompt utility, either directly or through a batch file
Using the Execute method in the DTS package object in a custom application
For debugging purposes, you can also individually execute each task in a package by right-clicking the task and then clicking Execute Step.
When you execute a package directly, it executes in the security context of the Windows user account that you used to log in to the computer that is executing the package. If you are at the server (either physically or through remote access software), the package executes on the server. If you are seated at a workstation, the package executes on the workstation. When the package is run as a scheduled job, the package always executes on the server and generally runs in the security context of the owner of the job. However, if the owner of the job executing the package is not a member of the System Administrator server role, the package executes in the security context of the SQL Server Agent Proxy Account. (For more information about running a package as a scheduled job, see the Microsoft Knowledge Base Article 269074 at http://support.microsoft.com/default.aspx?scid=kb;%5bLN%5d;269074 .)
Developers commonly run into issues when executing through scheduled jobs packages that they created and tested interactively on their workstations using DTS Designer. Since executing a package in the context of a job changes the execution location of the package from the developer s workstation to the server, problems frequently occur. Packages fail because text files that were local to the developer s workstation are not local to the server. Packages also fail because connections to data sources or destinations that succeeded for the developer fail for the owner of a job.
The parameters you must pass to execute a package with a number of global variables are difficult to generate manually. However, the DTSRunUI makes this task easy. In this procedure, you will learn how to use the DTSRunUI graphical utility to generate a DTSRun statement. In the next procedure, you will save this generated DTSRun statement into a batch file.
Click Start, and then click Run.
In the Open box, type DTSRunUI and click OK to display the DTS Run dialog box, in which you specify the package format using the Location list and also specify where the package is located.
You must specify the package location information before you can browse to and select the name of the package you want to execute. In this procedure, you will specify the PopulateTimeDimension package that you created in Chapter 2.
In the Location list, select Structured Storage File and then type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\PopulateTimeDimension.dts in the File Name box.
Click the ellipsis next to the Package Name text box to browse the list of packages stored in that structured storage file.
Since only one package ( PopulateTimeDimension ) is stored in this file and you want to execute the most recent version of it, you do not need to select a particular version.
Click PopulateTimeDimension and then click OK to display the DTS Run dialog box, which shows the package ID along with the date of the most recent package.
If you click Run at this point, this package will execute using the start date and end date values stored in the package s global variables. However, because you have already executed this package with these values and populated the TimeDim values based on them, you need to learn how to pass different global variable values at run time to control the package s functionality. You can also click Schedule to create a job that executes this package.
If you had selected a specific version of this package, the version ID would be displayed rather than the package ID. When creating a batch file that will execute a package, you frequently want the batch to always execute the most recent version of the package. Using the package ID rather than the version ID will accomplish this.
Click Advanced, type mypassword in the Password box, and then click OK to display the Advanced DTS Run dialog box, which enables you to select global variables that are defined in this package and provide run time values for them.
Click the first row in the Name column, and then select StartDate. Click the second row in the Name column, and then select EndDate.
The stored values for these variables are displayed. However, since you have already executed this package once using these variable values, you need to pass new values to this package at run time.
In the Value column for the StartDate global variable, type 1/1/1998 , and then in the Value column for the EndDate global variable, type 3/1/1998 .
Specifying a value for a global variable at run time does not affect the value of that global variable stored in the package. Rather, the new global variable value is used only at run time.
Click Generate to generate a DTSRun statement with a number of parameters. (You will learn about these parameters in the next procedure.)
You can also choose to encrypt this command. It is useful to hide parameters, such as password values, when you schedule the execution of the package. However, if you encrypt the command, you will not be able to modify global variable parameter values in the batch file for different executions of the package.
Copy the generated DTSRun statement with all its parameters to the clipboard and then click OK.
Highlight the complete DTSRun statement and then press Ctrl+C to copy the generated DTSRun statement.
Click Cancel, and then click Cancel again.
Although you could click Run to execute this package with these parameters, it is more useful to create a batch file that you can modify and run periodically with new global variable values whenever your data warehouse needs newer time values.
Now that you have generated the most difficult part of the DTSRun statement using DTSRunUI, you are ready to create the batch file that utilizes the statement parameters that you copied to the clipboard.
In this procedure, you will learn how to create a batch file that executes the DTSRun command line utility. You will then execute this batch file to execute the PopulateTimeDimension package from a command prompt using specific global variable values.
To make it easier to work with the DTSRun statement in Notepad, click Word Wrap on the Format menu.
On the Edit menu, click Paste.
The generated DTSRun statement appears in Notepad and contains the following parameters:
The /N parameter specifies the package name.
The /M parameter specifies the password required to execute the package ”either the owner or the user password.
The /G parameter specifies the GUID that identifies the package. If you do not specify a particular version of the package using the /V parameter, the most recent version of the package will be executed. Omitting the /V parameter enables you to update a package and have the most recent version execute without having to update your batch file or scheduled job.
The /F parameter specifies the file path and name for the package.
Each /A parameter specifies a global variable and its values. You can have multiple /A parameters.
The /W parameter specifies the Windows Event Log completion status.
Since this batch file contains the GUID that identifies the package, you will need to change the GUID in this file if you save this package with a different GUID. For example, if you save this package to a different location to eliminate earlier versions and then replace the original package with the newly saved package, the newly saved package will have a different GUID.
On the File menu, click Save As.
Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Save In list, type UpdateTimeDim.cmd in the File Name box, click All Files in the Save As Type list, and then click Save. Do not close Notepad.
Click Start and then click Run.
In the Run box, type cmd and then click OK.
In the command prompt window, type cd \Microsoft Press\SQL DTS SBS\DataMovementApplication and then press Enter.
At the C:\Microsoft Press\SQL DTS SBS\DataMovementApplication command prompt, type UpdateTimeDim.cmd and then press Enter.
DTSRun loads and executes the package, adding two months of new date values to the TimeDim table in the SBS_OLAP database. Do not close the command prompt window.
Switch to SQL Query Analyzer.
On the toolbar, click Clear Window.
In the query pane, type SELECT * FROM SBS_OLAP.dbo.TimeDim WHERE CalendarYear = 1998 and then click Execute on the toolbar.
The results pane displays the 59 rows that were added to the TimeDim table for the time members for the first two months of 1998.
You have successfully created a batch file that will enable you to easily add new time values to the TimeDim table as you add new monthly sales data to the SBS_OLAP database. Before you learn to add functionality to your data movement application, you need to learn about one more package feature ”package logging.