Logging - SQL Server 2008

SSIS includes advanced logging functionality that can be configured to track various runtime events. It is also possible to generate custom messages from within a running package. SSIS provides the following logging options:

  • Log events to text files
  • Log events that can be captured by SQL Profiler
  • Log events to a SQL Server table
  • Log events to the Windows Event Log
  • Log events to an XML file

The following exercise demonstrates how to access these options.

SSIS logging options

SSIS logging options

You can control which events are logged for each executable within the control flow. SSIS can log events such as errors, execution status changes, informational events, postexecution events, postvalidation events, preexecution events, prevalidation events, progress notifications, task failures, variable changes, and warnings.

You can also determine which columns are logged, including the date the log event occurred, the computer name where the log event occurred, the user who launched the package, the name of the container or task where the log event occurred, the package’s unique identifier, the unique identifier of the package execution, the event message text, the start time of the task/container execution, the end time of the task/container execution, and the elapsed time based on the start and end time. In this example, you will set up an Execute SQL task that will generate an error. You will enable error logging for the task, execute the package, and then examine the resulting Windows Event Log entries.

  1. In a new SSIS package, create an OLE DB connection manager to the SQL Server instance containing the AdventureWorks database.
  2. Drag an Execute SQL task onto the Control Flow design surface. Double-click the Execute SQL task.
  3. For the Connection property, select the AdventureWorks connection. In the SQLStatement property, type the following SELECT statement (the statement will cause a divide-by-zero error):
    SELECT 1/0
  4. Click OK.
  5. In the Control Flow design surface of the SSIS package, right-click an empty area and select Logging.
  6. In the Configure SSIS Logs: Package dialog box, enable logging by selecting the check box under Containers in the left pane (based on the name of your package), as shown in Figure.
  7. Configure SSIS Logs: Package dialog box

    Configure SSIS Logs: Package dialog box

  8. In the “Add a new log” section, select SSIS log provider for Windows Event Log from the Provider type drop-down list, and then click the Add button.
  9. In the “Select the logs to use for the container” section, select the check box of the new logging item.
  10. Adding Execute SQL task logging

    Adding Execute SQL task logging

  11. Click the Details tab and check the OnError event.
  12. Click the Advanced button. Notice that the events are preselected, as well as the associated columns that will be logged for each event, as shown in Figure. Click OK to set the defaults.
  13. Details tab of the Configure SSIS Logs: Package dialog box

    Details tab of the Configure SSIS Logs: Package dialog box

  14. Select Debug ➤Start Debugging. You will see the Execute SQL task fail.
  15. From Windows, select Start ➤Run. Type eventvwr and click OK.
  16. In Event Viewer, select Application Log. You will see three errors of varying granularity for the failed task. Click through each. Within the description of each, look for the error with a source name of Execute SQL Task. It is here you will see the “Divide by zero error encountered” error message that caused the task (and package) to fail, as shown in Figure.

Example of event logging from SSIS in Windows Event Viewer

Example of event logging from SSIS in Windows Event Viewer


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics