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.
Table of Contents
1. Enable the SSIS package logging in Visual Studio
To display the logging menu and parameters, follow these two steps:
- Right-click in the Control Flow workspace.
- Click on Logging to activate the log table.
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.
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.
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.
- OnError : Transcribes errors that occurred in Control Flow and Data Flow.
- OnPostExecute : Transcribes the end of tasks in the Control Flow.
- OnPreExecute : Writes the start of tasks in the control flow.
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.
Be the first to comment