The General tab has three sections of properties. Most of the properties on this tab are read-only; some of them can be set in other areas of the package. The first section contains the descriptors for the package, including the Name, Description, Package GUID, and Version GUID. The name is set when you save the package (see “Package Storage Locations” later in this chapter). You can add or edit the description of the package in this tab. The two GUIDs are used to identify the package and its current version. (You can store multiple versions in SQL Server Local Packages, Meta Data Services Packages, and the Structured Storage file.) The version GUID will be the same as the package GUID if it is the first and only version.

The next section is the Creation area, where you can find the creator of the package. The creator is stored in Windows networking domainusername format. The next field is the Win32 computer name of the machine on which the package was created. After that, you can find the date and time the package was created.

The Execution Parameters section allows you to set the Windows process priority. The default is Normal, but you can set it for High or Low. You also can set the maximum number of tasks to be executed in parallel. The number you use here can increase or decrease the efficiency of the package, depending on how the steps are set up. If you set this number too high and have a high number of steps being executed in parallel, it will significantly slow the execution of the package. If you set the value too low, it will negatively impact a package that has a number of steps set up for simultaneous execution.

In SQL Server 7.0, the Package Properties dialog box has only three tabs: General, Global Variables, and Advanced (see Figure 7.2). The logging features available in the Logging frame of the Logging tab in SQL Server 2000 are not available in SQL Server 7.0. (These features include package logging.) Error handling is on the General tab in SQL Server 7.0, whereas it is located on the Logging tab in SQL Server 2000. Another difference is the Auto Commit Transaction property on the Advanced tab in SQL Server 7.0. When this check box is checked, the SQL Server handles each SQL command in the package as a separate transaction. When the check box is cleared, an implicit transaction is assumed to include all the tasks until a commit or rollback from a workflow. If more steps occur after the commit or rollback, a new implicit transaction begins. (The workflow transaction properties are the same for both versions of SQL Server.) One other limitation of SQL Server 7.0 is the inability of DTS to support more than one simultaneous transaction.

Figure 7.2.  The Package Properties dialog box in SQL Server 7.0.

The Global Variables tab has a table that allows you to change the names, types, and initial values of these global variables. You can use these variables many places in the package. (See Chapter 4, “DTS Tasks,” specifically the section on the ActiveX task, for some of the types of usage available) The values that you set in this tab are saved with the package. This is also true if the value is changed during the execution of the package. If the value is changed, the new value will show up in this dialog box after the package has run.

You can use this attribute to set and recover values that have been used in the execution of the package. These variables are in scope for the duration of the execution of the package. This tab also allows you to enforce explicit declaration of all variables in all scripts in the package (similar to the Option Explicit declaration in Visual Basic).

You set package-logging properties in the Logging tab of the Package Properties dialog box. The DTS package log can be used to track down any problems or errors that occurred during the execution of a package. The log tracks the execution or lack of execution of individual steps in the package. You can see whether any given step succeeded, failed, or did not run. (Although steps that do not run are not listed in the log, their absence from the log shows that they did not run.) This information is stored in msdb tables in SQL Server (package information in the sysdtspackagelog table, step information in the sysdtssteplog table) or in SQL Server Meta Data Services (package information in Package Tab in the DTS Meta Data node of Enterprise Manager).

You can save the log in any instance of SQL Server 2000. You can use the properties to specify which server instance you will use and how you will access the server (Windows Authentication or SQL Server Authentication, username and password). In this frame you can also choose to fail the package on a log error. You might want to use this option if your process requires that the package be logged.

You can view the log by right-clicking the package in the detail of the Local Packages or Meta Data Services Packages in Enterprise Manager and choosing Package Logs from the shortcut menu. This brings up the DTS Packages Logs dialog box, which allows you to select and open any package log available in this location (see Figure 7.3). You can then view the log (see Figure 7.4), which displays the step details and allows you to view more information (if available) and the error information on steps that failed due to errors. Any step that is executed is displayed with a red cross for failure or a green check for success. Steps that are not run will not be included in the log.

Figure 7.3. List of available logs.

Figure 7.4. The package log details with error details showing.

Private Sub CustomTask_Execute(ByVal pPackage As Object, _ 
    ByVal pPackageEvents As Object, ByVal pPackageLog As Object, _ 
    pTaskResult As DTS.DTSTaskExecResult) 
... 
pPackageLog.WriteStringToLog "Task Successful" 
... 

Dim objLog 
Set ObjLog = DTSPackageLog 
ObjLog.WriteStringToLog "Task Successful" 

Table 7.1 defines the values in the Run Status column. The remaining columns add further detail about the step as it was executed (times, step names, and error information).

The Error Handling properties are located in the Logging tab of the Package Properties dialog box. You can set three properties. First, you can set the location of the error file.

Package Name: DTS Test 1 
Package Description: (null) 
Package ID: {805C300A-C3BC-4B5C-B068-F223E76C9B0C} 
Package Version: {A2E9689C-F3EC-4FD9-9CE3-48D912764D5F} 
Package Execution Lineage: {24890402-01BC-4F62-BEDC-CF8B3ACF667C} 
Executed On: STEVEHLAPTOP 
Executed By: SteveH 
Execution Started: 2/13/2001 11:14:18 AM 
Execution Completed: 2/13/2001 11:14:25 AM 
Total Execution Time: 7.03 seconds 

Package Steps execution information: 


Step 'DTSStep_DTSDataPumpTask_2' succeeded 
Step Execution Started: 2/13/2001 11:14:18 AM 
Step Execution Completed: 2/13/2001 11:14:24 AM 
Total Step Execution Time: 5.888 seconds 
Progress count in Step: 1 

Step 'DTSStep_DTSActiveScriptTask_1' succeeded 
Step Execution Started: 2/13/2001 11:14:18 AM 
Step Execution Completed: 2/13/2001 11:14:18 AM 
Total Step Execution Time: 0.631 seconds 
Progress count in Step: 0 

Step 'DTSStep_DTSSendMailTask_1' was not executed 

Step 'DTSStep_DTSExecuteSQLTask_1' failed 

Step Error Source: Microsoft JET Database Engine 
Step Error Description:The Microsoft Jet database engine could not find the object 
'test'. Make sure the object exists and that you spell its name and the path name 
correctly. 
Step Error code: 80040E37 
Step Error Help File: 
Step Error Help Context ID:5003011 

Step Execution Started: 2/13/2001 11:14:24 AM 
Step Execution Completed: 2/13/2001 11:14:25 AM 
Total Step Execution Time: 0.33 seconds 
Progress count in Step: 0 
******************************************************************************* 

Second, you can set the package to fail on the first error. When this option is selected, the package will result in failure if an error occurs in any step. This option is important if you are tracking the successful completion of the package for any reason. If you do not select this option, the package can have steps fail but will result in the successful completion of the package.

When this happens, a package that is using Execute Package tasks with precedence constraints may not work as expected. The Execute Package tasks will receive the success notice from the executed package even if steps in the package fail. This could also affect you if you are executing dependent steps that are running in parallel. To stop the parallel processes, you should fail the package when any step fails. You can also use this property to help manage transactions, as discussed in the following section.

Finally, you can set the package to write the completion status of the package to the application event log in Windows NT or Windows 2000 (see Figure 7.5).

Figure 7.5. Completion status written to the Windows application event log.

Event Type:   Information 
Event Source:   DataTransformationServices 
Event Category:   None 
Event ID:   80 
Date:   2/13/2001 
Time:   11:22:07 AM 
User:   N/A 
Computer:   STEVEHLAPTOP 
Description: 
The execution of the following DTS Package succeeded: 

Package Name: DTS Test 1 
Package Description: (null) 
Package ID: {805C300A-C3BC-4B5C-B068-F223E76C9B0C} 
Package Version: {A2E9689C-F3EC-4FD9-9CE3-48D912764D5F} 
Package Execution Lineage: {BB2E2B80-221F-40E6-B57A-8B4AC931698B} 
Executed On: STEVEHLAPTOP 
Executed By: SteveH 
Execution Started: 2/13/2001 11:22:04 AM 
Execution Completed: 2/13/2001 11:22:07 AM 
Total Execution Time: 3.365 seconds 

Package Steps execution information: 


Step 'DTSStep_DTSDataPumpTask_2' succeeded 
Step Execution Started: 2/13/2001 11:22:04 AM 
Step Execution Completed: 2/13/2001 11:22:07 AM 
Total Step Execution Time: 2.804 seconds 
Progress count in Step: 1 

Step 'DTSStep_DTSActiveScriptTask_1' succeeded 
Step Execution Started: 2/13/2001 11:22:04 AM 
Step Execution Completed: 2/13/2001 11:22:04 AM 
Total Step Execution Time: 0.391 seconds 
Progress count in Step: 0   
Step 'DTSStep_DTSSendMailTask_1' was not executed 

Step 'DTSStep_DTSExecuteSQLTask_1' failed 

Step Error Source: Microsoft JET Database Engine 
Step Error Description:The Microsoft Jet database engine could not find the object 
'test'. Make sure the object exists and that you spell its name and the path name 
correctly. 
Step Error code: 80040E37 
Step Error Help File: 
Step Error Help Context ID:5003011 

Step Execution Started: 2/13/2001 11:22:07 AM 
Step Execution Completed: 2/13/2001 11:22:07 AM 
Total Step Execution Time: 0.05 seconds 
Progress count in Step: 0 

DTS has the capability to use transactions with the packages. You can use transactions to keep data in multiple servers consistent, to incorporate multiple tasks or steps into a single unit, to put multiple transactions in a single package, or to control errors in a asynchronous environment. Transactions are not available for all tasks or connection types (see the sidebar “Supported Connection and Task Types” in this section), and for transactions to work, MS DTC must be running on the computer on which the package is to be executed. Many reasons for using transactions exist, but several properties need to be set up in the workflows and in the package for them to work correctly. When you use transactions, carefully analyze how you are setting up your packages and transaction properties to prevent many of the anomalies that can occur.

The Transactions section of the Advanced tab of the Package Properties dialog box has three properties that you need to set to use transactions in the package.

To activate transactions in the package, check the Use Transactions check box. Checking this option enables the other options in this section and enables the use of transactions in the workflows. If this option is not checked, any requests from the workflows to join a transaction will be ignored.

The next property to set is Commit on Successful Package Completion. When you choose this property, all open transactions will commit when the package completes if either of the following statements is true:

The last property to set on the Advanced tab is Transaction Isolation Level. This property determines the locking level used in the transactions to isolate the user from dirty reads, nonrepeatable reads, and phantom data. Table 7.2 shows the isolation levels with increasing degrees of protection, from Chaos to Serializable. The default in SQL Server including DTS is Read Committed. Refer to the isolation levels section of SQL Server Books On Line (BOL) for more information.

On the Advanced tab of the Package Properties dialog box, you should set the Fail Package on First Error option when you are using transactions. By selecting this option, you force a package to return an error on a failed step; therefore, it will correctly roll back the transaction. (Refer to “Error Handling” earlier in this chapter for more details.)

You also can set properties that affect transactions on workflows. By using the workflow properties, you can have steps join a transaction, roll back the transaction on step failure, and commit the transaction on successful completion of the step. See Chapter 6, “DTS Workflows,” for more information.

Transaction Scenarios

When you use transactions in DTS, you must be aware that different combinations of steps and workflows affect how the transaction will be initiated and committed. Sometimes, you need many steps participating in the same transactions; at other times, you need to use many transactions. Each situation has a different setup to keep you from getting anomalous data. Please understand that much care is necessary to set up transactions in DTS packages successfully.

Sequential Tasks

First, you should try to organize the package steps sequentially. This layout is the simplest one to use with transactions (see Figure 7.6). By using precedence constraints, you can establish that all tasks must succeed to commit the transaction. Set the package property to use transactions. Then set all the steps to join a transaction, if present, and to roll back the transaction on the step failure. In the final step, set the property to commit the transaction on the successful completion of the step.

Figure 7.6. Sequential tasks in a transaction.

Parallel Tasks

Sometimes, you may find it necessary to execute steps in parallel. If you need to have these steps be part of the same transaction, you need to plan the layout carefully to prevent anomalous results. First, use DTS package failure to roll back the transaction in the case of any step failure. Next, carefully consider whether you need more than one connection. If you need only one connection, SQL Server will serialize the execution of the steps in spite of the parallel configuration, which means that the precedence relationship will be enforced, but not the order of execution. If you need two connections, and these connections are on different data sources (in particular, different SQL Server instances), the tasks configured in parallel will execute as expected: in parallel. If the connections refer to the same data source—in this case, the same SQL Server instance—you can set the Execute on Main Package Thread workflow property or use precedence constraints to ensure that the package does not fail when the tasks try to join the transaction.

You need to manage the package carefully to prevent unexpected and undesirable results. Suppose that you have three tasks, A, B, and C. Two of these tasks execute from one connection, SQL1, and one executes from the second connection, SQL2 (see Figure 7.7).

Figure 7.7. Parallel tasks using transactions.

If tasks A and C execute simultaneously and task C fails, task A continues to successful completion.Task C’s failure causes the transaction to roll back, so the work from A and C is rolled back. However, because task A completed successfully, task B will start, joining a new transaction, and then will commit the transaction if the package property Commit on Successful Package Completion is set. For this reason, you must set Fail Package on First Error for the package. This property causes all steps that haven’t started not to execute; then the rollback occurs as expected on all tasks currently participating in the transaction.

Branching Workflows

If you find it necessary to initiate certain tasks based on the success or failure of a series of parallel steps, you will want to branch your processes based on the success or failure of the transaction. In the previous example, we had three tasks that were part of a transaction in a package that would fail on the first error. To branch the process, use the Execute Package task and precedence to determine the next step (see Figure 7.8). For this package, clear the Fail Package on First Error check box so that the failure of the Execute Package step will be returned and precedence followed. You also need to check Use Transactions for the package.

Figure 7.8. Branching transactions using an Execute Package task.

In Figure 7.8, you see that if the ABC package fails, an email task is executed, but on success, task D will execute. Table 7.3 shows the various properties you can set to have the tasks use transactions in different ways.

Table 7.3. Branching Transactions: Step Properties to Be Set

	Join Transaction	Commit Transaction	Rollback Transaction
Tasks A, B, C, D in same transaction	PackageABC Task D	PackageABC Task D	PackageABC Task D
Tasks A, B, C, in different transaction than task D	Task D	Task D	Task D

Checkpointing for Multiple Transactions

At times, you might be able to divide the work in a package logically into separate transactions. In that case, you would want to use a method of checkpointing to handle the transactions (see Figure 7.9). You set up the steps to participate in the first transaction and then, using precedence relationships, step to an ActiveX Script task. No code needs to be placed in this task, because it is only a placeholder. You set the workflow properties on the task to join the transaction and to commit the transaction upon the successful completion of this step. By doing so, you can insulate the transactions and have better control of the process. When the ActiveX Script task completes successfully, the next steps can continue, joining a new transaction if necessary.

Figure 7.9. Using ActiveX Script tasks as checkpoints.

Inheriting Transactions

In DTS, you can use the Execute Package task to inherit transactions. You do so by creating packages that use transactions and then running them in a parent package with the Execute Package task. By enabling the Join Transaction If Present feature in the Options tab of the Workflow Properties dialog box, a package can inherit the parent package’s transaction if the package is part of an Execute Package Task that has joined the transaction (Join Transaction If Present). When this situation occurs, all the steps in the subpackages that have joined the transactions are included in the transaction.

Using inherited transactions changes the way transactions are handled in the subpackages. Steps in the child package join the parent package transaction so that no new package transaction is created. The properties that call for committing the transaction on the successful completion of the step or package are ignored. No rollback will occur when the package completes even if an error occurs. However, if you set the workflow to rollback the transaction on step failure, it will rollback the parent transaction. Be sure to set the Fail Package on First Error property for the subpackages. This property prevents the Execute Package Task from returning success when a step in a subpackage fails.

For more information on DTS transactions, search for DTS transactions in Microsoft SQL Server BOL.

The remaining properties in the Advanced tab of the Package Properties dialog box are Lineage, Scanning Options, and OLE DB. The Lineage and Scanning Options are closely related to the Meta Data Repository, which is covered in Chapter 18, “Data Lineage.”The OLE DB property determines how OLE DB data objects are instantiated.

In DTS Designer, right-click the design sheet, and then click Disconnected Edit to bring up the Edit All Package Properties dialog box. The Edit All Properties dialog consists of two parts: The layout of the package appears in the left pane, and the properties related to the selected component in the component appear in the right pane (see Figure 7.10).

Figure 7.10. Edit All Properties.

Some of the properties in this Edit All Properties dialog box (such as task, step, and connection names) are not available in the Properties dialog boxes of the objects themselves. You can use Disconnected Edit to change the properties of a package when the source or destination is not available. DTS tries to connect to the data sources or destinations to validate the connection information; Disconnected Edit allows you to bypass this protection feature.

Using Disconnected Edit is dangerous because you can edit many properties in the package without DTS validating the changes. If you edit them here, there is no guarantee that the changes will work when the package is executed. Use Disconnected Edit only if you are very comfortable with most of the components of DTS and if this tool is the only way to edit the properties you need to modify.

You can save a package in a number of ways. Each storage location has certain advantages, which are discussed later in this chapter. When you save the package initially, or use the Save As option, the Save DTS Package dialog box allows you to save to different locations via the Location drop-down menu (see Figure 7.11). You need to understand that each time you choose Save As from the File menu, the package GUID will change, not just the version GUID.

Figure 7.11. Package storage locations.

name       DTS Test 1 
id        805C300A-C3BC-4B5C-B068-F223E76C9B0C 
versionid     2896CDE1-47C8-4FB0-9A93-965B119AE15A   
description    Test package 
categoryid    B8C30002-A282-11D1-B7D9-00C04FB6EFD5 
createdate    2000-12-05 01:11:05.003 
owner       MINNEAPOLISSteveH 
packagedata    {Image} 
owner_sid     0x01 
packagetype    0 

Structured Storage File

You can use this option to save the DTS package to a .dts file. The Structured Storage File is the most portable of the various storage options, because you can copy and transfer the file quite easily. The file is called a structured file because it uses COM-structured storage. If you are doing a great deal of package editing and then saving the package to the same file, the file can become quite large because each version is saved. When you save the package in a structured storage file, you are prompted for a filename. If you create a new file, the package has only one version: the current one. If you choose an existing filename, you will be prompted to add the current package to the existing file, creating a new version of the file. In the SQL Server Enterprise Manager console tree, right-click on the Data Transformation Services folder. Then click Open Package from the context menu. In the Select File dialog box, click the file you want, and then click Open. If multiple versions of the DTS Package were created, the Select Package dialog box appears where you can click the package or package version you want to open (see Figure 7.12).

Figure 7.12. Opening a package stored in a structured storage file

When you save a package to SQL Server or a structured storage file, you can secure it beyond the Windows authentication or SQL server authentication levels. When you save the package, you can assign owner and user passwords. When you assign an owner password, you need to use this password to edit or run the package. To assign a user password, you need to assign an owner password. Using both passwords allows a user to run the package, but the user will not have permission to edit or to open the package. Microsoft recommends that you apply this security option to further secure both the package and the database, especially if you are not using Windows authentication for data access.

You can execute a package from three locations: Enterprise Manager, a DTS tool, and the command line. In Enterprise Manager, you can execute local and Meta Data Services packages by right-clicking on the selected package located in the details pane of the Enterprise Manager Console tree and selecting Execute from the context menu.

You can execute a package from the two primary DTS tools: the DTS Designer and DTS Import/Export Wizard. From an open package, you can execute the package from one of three locations in the DTS Designer. First, you can click the Execute button on the toolbar. Second, you can choose Execute from the Package menu. Third, you can execute the package by right-clicking a blank space on the DTS Designer window and choosing Execute from the shortcut menu. You also can execute a package at the end of the DTS Import/Export Wizard by selecting the Run Immediately option and clicking Finish.

Microsoft provides two utilities for executing the package from a command line. The DTS Run utility can be accessed by typing dtsrunui without any command switches. This will bring up the DTS Run dialog box (see Figure 7.13), which allows you to select the location of the package, the package name and version, and the location details. After the package has been selected you can Run or Select a scheduled task to run. (The Run and Schedule buttons remain disabled until a valid package version is selected.)

Figure 7.13. DTS Run dialog box.

Clicking the Advanced button gives you access to global variables, logging information, and command-line information. You can edit the global variables temporarily by adding new ones or editing existing ones. These global-variable changes are valid only for the current DTS Run session and are not saved when the session is closed.

You also can select the log file and specify whether to write the completion status of the package to the event log. For the command line, you can choose to encrypt the command for use in the next utility, dtsrun.exe. In addition, you can have the line generated in SQL Server 7.0 format. When you click the Generate button, the utility will create a command line for dtsrun.exe that has the selected properties applied. You can use this generated command line in the next utility described by copying and pasting it into a command line.

DTSRun /S "(local)" //N "DTS Test 1" //V "{A2E9689C-F3EC-4FD9-9CE3-48D912764D5F}" //W 
"0" //E 

DTSRun /S "(local)" //N "DTS Test 1" //V "{A2E9689C-F3EC-4FD9-9CE3-48D912764D5F}" //E 

DTSRun 
/∼Z0x4050833D40908D31188F436727E340111E360047CC10CBD59BC560636ABBED22B630673340E95 
049A1FC69CE0D3AD96854F2101F3C3C763B24F05A0E779B2C395BE7D572B60289D3ED997FF2B3ECD0F 
5EF0C89358B354DFF1B6A21189C3114774E283B4486379A18A06195C5FDB5C4E52E2C498359EF43DA4 
20D288956D4DC2FBA62214FC8696EDAC8B08F2E09E860768DA74C59A4210AC1CB09714F7194E7CC12D 
9C563C2536029F25662CD72DA508B5098CC8CA960FAA083CC96 

DTSRun /∼S 0xBD02B5BE90E2B90926828A12F24175AA /∼N 
0x64C9C49314125FB980EC63C075EACCD8FCBD1CF0E8A295B5 /∼V 
0xD751BE59BF32E3D7BB500A00CE73B349219F0597C886C9C9FFAD32B71BC6C02648EF197C56B976B2 
92871661BA8FA7A2089F804E9D9A58EB66C920CE6F82CC21008194D41AB757A2CF6A2662FFDB60E9 
/E 

The other utility is dtsrun.exe. This utility has no graphic components and several switches (see Table 7.4) that give you more control over the execution of the package. You can generate the command line with switches by using the dtsrun utility as mentioned in the previous paragraph. From this utility, you can execute packages stored in the SQL Server msdb, structured storage files, and SQL Server Meta Data Services.

All the execution methods described in this section return various levels of detail about the success or failure of the package that is being executed. When you are executing a package from the DTS tools, the Enterprise Manager, or the DTS Run utility (dtsrunui.exe, not dtsrun.exe), a dialog box opens when the package runs. The Executing DTS Package dialog box (see Figure 7.14) shows each step in the package and the status, start time, finish time, and execution time for each step. (The status text varies according to the task being executed.)

Figure 7.14. Executing DTS Package dialog box.

If the package has Execute Package Tasks, this dialog box does not display the steps from each Execute Package Task. Some information from the steps is displayed as the tasks execute in the status line above the progress bar. From this dialog box, you can cancel the package. The package does not quit running immediately, however; it waits until any steps that do not support canceling complete. To view this information later, you need to refer to the package log.

If an error is encountered when you use the dtsrun utility from the command line, the package will stop executing and display error information and usage instructions. No dialog box is displayed when you use this utility. This is very helpful when you are trying to execute the package by using batch files or scheduled tasks in Windows and do not want any graphical interfaces involved.

You can schedule packages to run at specific times and in specific patterns. If you need to run a package nightly, monthly, or even hourly, you can do so by setting up a SQL Server Job. The easiest way to do this is by right-clicking on the package in the detail pane of the Local Packages or Meta Data Services Packages folder in the SQL Server Enterprise Manager console tree. If the SQL Server Agent is not running when you try to schedule it, you will receive a warning; then you can proceed to set up the schedule (see Figure 7.15). You can set a Recurring Job Schedule the same way that you would set up a recurring job in SQL Server.

Figure 7.15. Scheduling a package.

After you set up a job, you can edit it in the Management/SQL Server Agent/Jobs section of Enterprise Manager. You also can view the Job History here. However, editing the actual job step is difficult because the wizard uses the encrypted dtsrun command line. On the other hand, you can create the job manually in the Jobs section of the Enterprise Manager by creating an Operating System Command (CmdExec) step that uses the dtsrun utility. You can find details on the various formats and syntaxes used with dtsrun.exe in “Package Execution” earlier in this chapter. (Remember that you can use the DTS Run utility, dtsrunui.exe, to generate the command line.)

To create and execute this SQL Server job successfully, the SQL Server agent must have the security permissions to run operating-system commands from within SQL Server (via CmdExec or xp_cmdshell). If you need to schedule your package but cannot grant the necessary permissions to the agent, see the sidebar “Alternative to SQL Server Jobs for Scheduled Packages.”

SQL Server jobs also have logging available. The first level of job logging is displayed in the detail view of the job listing in the Jobs detail of the Enterprise Manager. You can see the details of the last job, run as well as details on the current and future status of the job, including whether the job is enabled, runnable, or scheduled and when it is scheduled to run again. The next level of logging allows you to view the job history, which contains the detailed history of the job. Here you can look for specifics about why a job fails. Checking the job history for errors before troubleshooting your package is important. If you were able to execute the package from Enterprise Manager but not through the scheduler, you are most likely to find the problem in the job history. For more information about SQL Server jobs, refer to the BOL topic “Creating Jobs.”

Deleting a package is fairly simple. To delete the entire package from SQL Server or Meta Data Services, right-click the package and choose Delete from the shortcut menu. You need to be the owner or a member of the sysadmin fixed server role to delete the package or a version of the package. You can delete any version of a package that has been saved to SQL Server. You can delete the most recent package only if it is stored in Meta Data Services. To delete versions, right-click the package and choose Versions from the shortcut menu. When the DTS Package Versions dialog box opens, select the package version and click Delete. You cannot delete individual versions from packages stored in a structured storage file or a Visual Basic file.

Using the information provided in this chapter, you should be able to make good decisions on which package properties are necessary to fulfill your needs. Whether the package needs to be portable (storage locations), needs to implement transactions, or needs to be executed on a schedule, you should be able to choose the options necessary to get the desired result. For information on advanced logging of packages, see Chapter 14, “Custom Error Logging with DTS,” and Chapter 18, “Data Lineage.”