Showing results for 
Search instead for 
Did you mean: 

How to enable ETL Logging (Verbose) ?

Community Manager

How to enable ETL Logging (Verbose) ?

In order to get proper confirmation you want to temporarily modify the log level for the ETL job to verbose. See this article for steps. This will cause a message to be logged for the forced package format upgrade. The first option for the invocation of the ETL job (in the SQL Agent job step or manually via dtexec) is "/REP EW"; you'll want to change that to "/REP V" for verbose reporting instead.

Here's the relevant output I got at DHL with verbose logging enabled:

Microsoft (R) SQL Server Execute Package Utility

Version 12.0.4100.1 for 64-bit

Copyright (C) Microsoft Corporation. All rights reserved.

Started: 22:30:26


Info: 2017-02-24 23:10:16.32

Code: 0x40016019


Description: The package format was migrated from version 3 to version 8. It must be saved to retain migration changes.

End Info

Info: 2017-02-24 23:10:26.21

Code: 0x4001F300

Source: Main

Description: Succeeded in upgrading the package.

End Info

Also, you will have to modify the history settings for the SQL Server Agent if they were left at default: By default the Agent allows a maximum of 100 rows of output per job invocation for a total of 1000 rows. Using the default /REP EW setting our ETL job already produces over 400 rows of output and warnings (so the log you're waiting to receive may not contain any relevant information at all); using verbose logging you will get over 1000 rows of output.

To change the default settings open the properties of the SQL Server Agent in Management Studio's Object Explorer and switch to the History tab. There are two settings, "Maximum job history log size" and "Maximum job history rows per job". I recommend 10,000 and 2,000 for normal operations and 20,000/5,000 when using /REP V.

If you're trying to direct the customer to implement these changes without having access to the system yourself there's also a way of making the same change via a SQL stored procedure:

USE msdb


EXEC sp_set_sqlagent_properties @jobhistory_max_rows=20000, @jobhistory_max_rows_per_job=5000

Tags (1)