Preventing PackageStart and PackageEnd events being output in SQL Server SSIS logs

There doesn’t seem to be a way to turn off the logging of PackageStart and PackageEnd events in the SSIS Logging GUI in Visual Studio (correct me if I’m wrong). If you don’t care about these events and your ETL runs often this can result in a lot of excess log entries. Thankfully if your logging to SQL Server rather than a CSV file for example you can simply customise the stored procedure that SSIS uses to write log entries.

Modifying Sp_SSIS_Addlogentry to not write PackageStart and PackageEnd events

Sp_SSIS_Addlogentry is the stored procedure SSIS uses to log entries in your SQL Server DB. It will attempt to create this sproc and corresponding log entry table (sysssislog) if they don’t currently exist. If they do exist SSIS will not overwrite them so your customisations are safe.

If the Visual Studio GUI doesn’t provide the level of granularity you need to turn logging of certain events on or off you can just ignore these events manually by altering the Sp_SSIS_Addlogentry stored procedure. For example line 16 and 17 below shows both PackageStart and PackageEnd being ignored and the sproc returning before the insert statement.

Preventing PackageStart and PackageEnd from showing in the logs

Useful customisations to Sp_SSIS_AddLogentry

By modifying the Sp_SSIS_AddLogentry sproc you have full ability to intercept all logged events and do what you want with them. Some other customisations I’ve done in the past include:

  •  Remove endtime from the sysssislog table as its not implemented and always matches starttime.
  •  Log the dates as UTC as they’re currently stored in DB local time.
  •  Control the frequency of certain kind of events such as PipelineComponentTime. PipelineComponentTime is very useful for seeing how long each of your tasks take but it does log a lot of entries. If you only wanted snapshots you could just log every X amount of minutes for example.
  • Rename the underlying log table.

Leave a Reply

Your email address will not be published. Required fields are marked *