Sql-server – Configuring 2017 SQL Server SSISDB Server-wide Default Logging Level does not seem to work

loggingsql serverssis

Using SSMS, I have changed the SSIS Catalog Logging Level from Basic to Performance to a Custom Logging Level in a 2017 SQL Server Instance and then I run the report: "All Executions". It doesn’t seem to matter what Logging Level I am at, the amount of data/detail that is included in the SSIS Catalog Report remains the same. I would think the amount of detail would change with each Logging Level.

If the SSIS Catalog Reports do not change when the Logging Level changes, then how do I see the changes to the Logging Levels? Otherwise, if the SSIS Catalog Reports are suppose change, can someone please point me in the correct path to get the SSIS Catalog Reports to change?

Best Answer

As BCM indicates, that default will apply to new executions. Your job in SQL Agent likely has the earlier default level baked into the execution and so it's overriding the system default. In the Advanced properties, you'd switch it to the new value.

When you manually run a job, this is the syntax of the steps it takes (agent job does the same behind the scenes)

DECLARE @execution_id bigint;

EXEC SSISDB.catalog.create_execution
    @package_name = N'Package.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'FolderName'
,   @project_name = N'ProjectName'
,   @use32bitruntime = False
,   @reference_id = NULL;

SELECT
    @execution_id;

-- 0 = None
-- 1 = Basic
-- 2 = Performance
-- 3 = Verbose
-- 4 = Runtime Lineage
-- 100 = Custom
DECLARE @var0 smallint = 1;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0;

EXEC SSISDB.catalog.start_execution
    @execution_id;
GO

If you use the custom/ 100 logging value, then you also need to specify what custom logging level you want to use. Assuming I created a log level called "New Custom Logging Level", I'd add set this parameter before invoking start_execution

DECLARE @logLevelName SQL_VARIANT = N'New Custom Logging Level'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @execution_id
,   @object_type = 50
,   @parameter_name = N'CUSTOMIZED_LOGGING_LEVEL'
,   @parameter_value = @logLevelName;

MSDN set_execution_parameter_value

Tim Mitchell has a good article on setting the execution parameter values

Related Question