Testing Jobs

Objectives

After completing this lesson, you will be able to:

  • Test jobs

Job Testing

Setting Traces

When a job is executed, a trace log is generated, giving information on each step.

The level of details displayed depends on some options that can be set in the job or execution properties.

Here are some of the available trace options:

TraceDescription
RowWrites a message when a transform imports or exports a row
Work flowWrites a message when the work flow description is read from the repository, when the work flow is optimized, when the work flow runs, and when the work flow end
Data flowWrites a message when the data flow starts and when the data flow successfully finishes or terminates due to error
TransformWrites a message when a transform starts and completes or terminates
SQL readersWrites the SQL query block that a script, query transform, or SQL function submits to the system and writes the SQL results
TablesWrites a message when a table is created or dropped
Scripts and script functionsWrites a message when a script is called, a function is called by a script, and a script successfully completes
Trace parallel executionWrites messages describing how data in a data flow is parallel processed
Stored procedureWrites a message when a stored procedure starts and finishes, and includes key values
Audit dataWrites a message that collects a statistic at an audit point and determines if an audit rule passes or fails

Setting Traces in Job Execution Properties

  • Determines what information is written to the log
  • Can be performed temporarily (Execution Properties ) or persistently (Job Properties )
  • Can generate single traces or traces for every row (for example, Trace Row, SQL...)

Once the job is started, you can select the Trace , Monitor , and Error icons to view the log files, which are created during job execution.

Let me show you how to setup traces:

Examining Trace Logs

Use the trace logs to determine where an execution failed, whether the execution steps occur in the order you expect, and which parts of the execution are the most time consuming as shown in the following figure.

Examining Monitor and Error Logs

Use monitor logs to quantify the activities of the components of the job as shown in this other figure. It lists the time spent in a given component of a job and the number of data rows that streamed through the component.

Use the error logs to determine how an execution failed. If the execution completed without error, the error log is blank.

Log files

As a job executes, Data Services produces 2 or 3 (in case of errors) log files, which are, by default, set to display automatically in the workspace. But you can also view them from the Monitor and Log tabs of the project area.

Using the Monitor Tab

The Monitor tab lists the trace logs of all current or most recent executions of a job.

The traffic-light icons in the Monitor tab can be read as follows:

  • A green light indicates that the job is running.

    Right-click and select Kill Job to stop a job that is still running. After selecting Kill Job , the job icon becomes a yellow triangle with an exclamation point in the middle.

  • A red light indicates that the job has stopped.

    Right-click and select Properties to add a description for a specific trace log. This description is saved with the log which can be accessed later from the Log tab.

  • A red cross indicates that the job encountered an error.

Using the Log Tab

Select the Log tab to view a job’s log history.

To find your job's log file:

  1. Select the job for which you want to view the logs.
  2. In the workspace , in the + dropdown list, select the type of log you want to view.
  3. In the list of logs, double-click the log to view details.
  4. To copy log content from an open log, select one or more lines and use the key command [CTRL+C ].

Log in to track your progress & complete quizzes