0

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?

3
  • 1
    It's not clear from your question, but are these new executions that you are looking at after the Logging Level? If they're older executions, the data is still in the SSISDB tables, so they would still report with what was there for that OperationID.New executions should reflect the new Logging Level. Logging Level is actually set on the Advanced tab on the job that can run packages, so you may have to switch it there too, if you have agent jobs set up already.
    – BCM
    Oct 21, 2020 at 12:21
  • The new executions are after the change in the Logging Level. So, what you are saying, is that changing the Server Level Logging Level may not change the Logging Level on already existing steps/jobs?
    – steveb
    Oct 22, 2020 at 15:10
  • Correct @billinkc has the correct answer. On the individual job step, click the Configuration tab, then the Advanced tab. In the middle there will be a drop down for Logging level, which will be set to default. Change it to your new one.
    – BCM
    Oct 22, 2020 at 18:30

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.