Due to its simplicity, I’m a fan of the default, in-built “SSIS log provider for SQL Server” logging, which writes to the SSIS database
SSISDBwhen the package is deployed to a SQL Server. This logging comes out of the box with very little setup required, and can be supplemented by custom messages – for instance, using
Dts.Login a script task as per https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/logging-in-the-script-task.
Best of all, because log messages are written to tables in the
SSISDBdatabase, end-users can run a query or report to troubleshoot errors.
Read on for a query which pulls the last seven days worth of error information from the built-in log.