Enable Logging for Package Execution on the SSIS Server
Applies To: SQL Server 2016
This topic describes how to set or change the logging level for a package when you run a package that you have deployed to the Integration Services server. The logging level you set when you run the package overrides the package logging you configure at design time in SQL Server Data Tools (SSDT). See Enable Package Logging in SQL Server Data Tools for more information.
In SQL Server Server Properties, under the Server logging level property, you can select a default server-wide logging level. You can pick from one of the built-in logging levels described in this topic, or you can pick an existing customized logging level. The selected logging level applies by default to all packages deployed to the SSIS Catalog. It also applies by default to a SQL Agent job step that runs an SSIS package.
You can also specify the logging level for an individual package by using one of the following methods. This topic covers the first method.
Configuring an instance of a package execution by using the Execute Package dialog box
Setting parameters for an instance of an execution by using the catalog.set_execution_parameter_value (SSISDB Database)
Configuring a SQL Server Agent job for a package execution by using the New Job Step dialog box.
In SQL Server Management Studio, navigate to the package in Object Explorer.
Right-click the package and select Execute.
Select the Advanced tab in the Execute Package dialog box.
Under Logging level, select the logging level. This topic contains a description of available values.
Complete any other package configurations, then click OK to run the package.
The following built-in logging levels are available. You can also select an existing customized logging level. This topic contains a description of customized logging levels.
|None||Logging is turned off. Only the package execution status is logged.|
|Basic||All events are logged, except custom and diagnostic events. This is the default value.|
|RuntimeLineage||Collects the data required to track lineage information in the data flow. You can parse this lineage information to map the lineage relationship between tasks. ISVs and developers can build custom lineage mapping tools with this information.|
|Performance||Only performance statistics, and OnError and OnWarning events, are logged.|
The Execution Performance report displays Active Time and Total Time for package data flow components. This information is available when the logging level of the last package execution was set to Performance or Verbose. 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 components only when the logging level of the package execution is set to Performance or Verbose.
|Verbose||All events are logged, including custom and diagnostic events.|
Custom events include those events that are logged by Integration Services tasks. For more information about custom events, see Custom Messages for Logging.
An example of a diagnostic event is the DiagnosticEx event. Whenever an Execute Package task executes a child package, this event captures the parameter values passed to child packages.
The DiagnosticEx event also helps you to get the names of columns in which row-level errors occur. This event writes a data flow lineage map to the log. You can then look up the column name in this lineage map by using the column identifier captured by an error output. For more info, see Error Handling in Data.
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 that the DiagnosticEx event does not preserve whitespace in its XML output to reduce the size of the log. To improve readability, copy the log into an XML editor - in Visual Studio, for example - that supports XML formatting and syntax highlighting.
The catalog.execution_data_statistics view displays a row each time a data flow component sends data to a downstream component, for a package execution. The logging level must be set to Verbose to capture this information in the view.
You can create customized logging levels that collect only the statistics and events that you want. Optionally you can also capture the context of events, which includes variable values, connection strings, and component properties. When you run a package, you can select a customized logging level wherever you can select a built-in logging level.
To create and manage customized logging levels, in SQL Server Management Studio, right-click on the SSISDB database and select Customized Logging Level to open the Customized Logging Level Management dialog box. The Customized Logging Levels list contains all the existing customized logging levels.
To create a new customized logging level, click Create, and then provide a name and description. On the Statistics and Events tabs, select the statistics and events that you want to collect. On the Events tab, optionally select Include Context for individual events. Then click Save.
To update an existing customized logging level, select it in the list, reconfigure it, and then click Save.
To delete an existing customized logging level, select it in the list, and then click Delete.
Permissions for customized logging levels.
All users of the SSISDB database can see customized logging levels and select a customized logging level when they run packages.
Only users in the ssis_admin or sysadmin role can create, update, or delete customized logging levels.