Enable SSIS package logging in SQL Server

Configure the management of logs generated by SSIS packages in 3 steps to handle errors and warnings.

There are different solutions to enable SSIS package logging, such as storing them in the sysssislog table, in SQL Server. Configuring log files allows you to monitor the execution of SSIS packages during batch runs. The first is to do it manually in Microsoft SQL Server Management Studio, from the job history. The second solution is to use SQL jobs to populate a log table. Finally, the third solution is to use SSIS log files.

A log file system or history log allows you to track all important events. Here is a step-by-step guide to setting up Integration Service execution logs. In addition, the solution using SQL tasks is the most frequently encountered. It allows you to search for events directly in a SQL Server database. Conversely, the solution using SSIS logs is not well known.

Using the sysssislog table to log the errors and warnings

The sysssislog table is a component created in the msdb database when Microsoft SQL Server Integration Services (SSIS) is installed. This table plays a critical role as it records a single row for each log entry that is produced during the execution of packages, or their tasks and containers. This logging mechanism allows for the tracking and troubleshooting of the run-time events.

If the logging configuration is activated and uses a different SQL Server database, then a new sysssislog table with the same structure, is generated in the target database. This provides flexibility in terms of where the log data is stored, making it adaptable to various infrastructure requirements and preferences. The table stores selected errors, warnings and events during the package execution.

1. Enable the SSIS package logging in Visual Studio

To display the logging menu and parameters, follow these two steps:

  1. Right-click in the Control Flow workspace.
  2. Click on Logging to activate the log table.
Right-click from Visual Studio to configure the SSIS log.
Right-click in the package to configure the SSIS log..

2. Select the SSIS log provider type for SQL Server

Once in the Configure SSIS Logs window in the Providers and Logs tab configure the window as below. For the type of log storage, several choices are offered, in this example, choose the SQL Server type. In this example, choose the SQL Server type, i.e. SSIS Log provider for SQL Server to store the logs in a table. Then choose the configuration.

Select the type of log provider for SQL Server here it is the dbo.sysssislog table
Select the type of log provider for SQL Server

Note that it is not possible to choose the name of the table to store the Integration Services logs. The SSIS log table is called dbo.sysssislog.

Regarding data manipulation with SQL Server, here is how to use the SQL Server PIVOT.

How to use the SQL Server PIVOT query to convert rows to columns ?

3. Select the SSIS events logged during the package execution

Finally, it is possible to customise the monitoring of treatments in the Details tab. Here is a chosen configuration to avoid generating too many logs. With only the options OnError, OnPostExecute and OnPreExecute.

  1. OnError : Transcribes errors that occurred in Control Flow and Data Flow.
  2. OnPostExecute : Transcribes the end of tasks in the Control Flow.
  3. OnPreExecute : Writes the start of tasks in the control flow.
Choose the three events OnError, OnPreExecute and OnPostExecute
Choose the three events OnError, OnPreExecute and OnPostExecute

However, it is possible to configure the SSIS package log to be enhanced according to the needs of the project by ticking specific events to be tracked. From the Configure SSIS Logs window, select the key events. It is recommended to configure the SSIS log with the necessary information only, so as not to have too verbose a result. As a reminder, selecting all events makes the log long and difficult to analyse.

That is for a simple and efficient way to enable SSIS package logging in SQL Server to monitor packages execution and status. Finally, here is an article with the list of SSIS error codes found in SSIS logs.

Liste de toutes les erreurs SSIS

Leave a Comment

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