Log records provided by SSIS

The logging functionality provided by SSIS, SQL Server 2012 is configured in three ways: configured on the package, configured on the Server of the package release, and configured logging on the job agent when new step is added.

One, configure the log on the package to record the event log for SSIS

Choose SQL Server where log exists, and SSIS will automatically create a log table Dbo.sysssislog. You can also view event logging when the package executes by using the Log Events submenu in the SSIS menu.

1, click Logging on the SSIS menu

2, select provider Type, stored in different media, need to record is container,package or task in run time generated event Log, so you must select event Host in the left containers.

Integration Services includes the following log providers:

• The text File log provider, which writes log entries to ASCII Text files in a comma-separated value (CSV) format. The default file name extension for this provider is. Log.

• The SQL Server Profiler log provider, which writes traces that can view using SQL Server Profiler. The default file name extension for this provider is. TRC. Note the cannot use of the SQL Server Profiler log provider in a package that's running in 64-bit mode.

• The SQL Server log provider, which writes log entries to the sysssislog table in a SQL Server database.

• The Windows Event Log provider, which writes entries to the application log in the Windows event log on the local computer .

• The XML file log provider, which writes log files to an XML file. The default file name extension for this provider is. Xml.

SSIS automatically uses links to create a system Tables:dbo.sysssislog to store logs.

!--? Xml:namespace PREFIX = "[Default] http://www.w3.org/1999/xhtml" NS = "http://www.w3.org/1999/xhtml"/-->sysssislog tabl E with this format was created in the specified database.

3, select the events of the record, click the Advanced button for more detailed configuration

The three fields in the following table are not displayed in detail, but are recorded in a log.

Element	Description
StartTime	The time at which the container or task starts to run.
EndTime	The time at which the container or task stops running. This feature was not implemented. The value in the Endtime column was always the same as the value in the starttime column.
Datacode	An optional integer value this typically contains a value from the Dtsexecresult enumeration that indicates the result of Running the container or task: 0-success 1-failure 2-completed 3-canceled

Second, configure logging on the server that executes the package

1, Open is catalogs, select the package that requires log configuration, click the Execute menu

2. Open the Advanced tab to update the level of the package log by modifying the logging level

To set or change the logging level for a package when you run a package so you have deployed to the integration Services Server. The logging level your set when you run the package overrides the package logging you configure using SQL Server Data Tools (SSDT).

Logging level	Description
None	Logging is turned off. The package execution status is logged.
Basic	All events is logged, except custom and diagnostic events. This is the default value.
Performance	Only performance statistics, and OnError and onwarning events, is logged. The Execution performance report displays Active time and total time for package data flow components. This information are available when the logging level of the last package execution were set to performance or Verbo Se. For more information, see Reports for the integration Services Server. The Catalog.execution_component_phases view displays the start and end times for the data flow components, for each phase of an execution. This view displays this information for these and the the logging level of the package execution are set to performance or Verbose.
Verbose	All events is logged, including custom and diagnostic events. An example of a diagnostic event, is the Diagnosticex event. Whenever an Execute package task executes a child package, the IT logs this event. The event message consists of the parameter values passed to child packages The value of the message column for Diagnosticex is XML text. . To view the message text for a package execution, query the Catalog.operation_messages (SSISDB Database) view. Note Custom events include those events that is logged by integration Services tasks. For more information, see Custom Messages for Logging. The Catalog.execution_data_statistics view displays a row each time a data flow component sends data to a downstream compo Nent, for a package execution. The logging level must is set to Verbose to capture this information in the view.

Third, configure logging when new step is on the job agent

Log records provided by SSIS