This article gives an overview of the different methods of SQL Server SSIS Package Logging.
Introduction
Event logs are essential for database administrators. It helps us to troubleshoot the issues or get an insight into running processes. We also require performing Root Cause Analysis for any critical issues. We use SQL Server Integration Services for deploying ETL packages. These ETL packages help for data load, automation various tasks in SQL Server.
Usually, we deploy the SSIS packages using a SQL Agent job and configure it to run as per the defined schedule. Once the SSIS package fails in the SSDT or BIDS, we can check the progress tab to look into the detailed error message. It is not an easy task to identify the error message if the package is running using a SQL Agent Job.
Different methods of SSIS Package Logging
We can use the following SSIS Logging methods for troubleshooting purposes.
- SSIS packages log providers
- Custom Logging messages using scripts in the Execute SQL Task
- In the project deployment model from SQL Server 2012, the SSIS catalog provides the execution logs in the SSISDB database
In this article, we will explore the SSIS package log providers.
The SSIS package log providers
For the demonstration, let’s create a SSIS package to execute the SQL Script. In the SQL Server Data Tools, create a new project for integration services. I added a new SSIS package to an existing project.
Configure a demo project
Drag an Execute SQL Task in the Control Flow.
We require configuring the Execute SQL Task. Double-click on this task to open the Execute SQL Task editor.
Click on the drop-down in the Connection and go to New Connection. It also shows the existing connections to use. Let’s use this connection in this article. If you want to create a new connection, you can provide the required details such as SQL Instance name, database name, and authentication in the connection window.
In the SQL Statement, specify the script you want to execute.
Click Ok and configuration is now completed for the Execute SQL Task.
In the Solution Explorer, Right-click on the SSIS package and click on Execute.
The Red-Cross icon on the execute SQL Task shows that the package execution failed.
Click on the Progress tab for the detailed error message. By looking at the following screenshot, we can identify the error message.
We want a similar kind of error message every time the package executes in SQL Agent job as well. We need to configure the SSIS Logging.
Configure an SSIS package logging
Let’s configure SSIS Logging in this package.
Right-click on the control flow area, and it gives you a list of options.
Click on Logging, and it opens Configure SSIS Logs window.
In the bottom message bar, it gives a message ‘to configure unique logging options, we need to enable logging for it in the tree view’.
Put a tick in the project checkbox and it enables the logging for this SSIS package.
On the right-hand side, we can see two tabs for configuration.
Provider and Logs in the SSIS package logging
In this tab, we select the SSIS logging provider type and their configurations.
We have following SSIS log providers available here.
- SSIS log provider for Text files: We use this log provider to store information in a TXT or CSV format. Usually, developers use this format to store logs information
- SSIS log provider for SQL Server: We can store the SSIS logs in the database tables. It makes it easy to query the logs using the t-SQL queries
- SSIS log provider for Windows Event log: We can use this log provider to send information in the application event viewer. The source name for these events is SQLISPackage110. We do not require any separate configuration to use this provider
- SSIS log provider for SQL Server Profiler: DBA usually use SQL Profiler to log queries and investigate issues in SQL Server. We can use this log provider to produce a SQL profiler trace. We can specify a .trc extension to open it with SQL profiler and view the package execution systematically
- SSIS log provider for XML files: This log provider stores the package logs in the XML files. We can configure the XML files and open the logs in tools such as browser, notepad or Visual Studio
Details section in the SSIS package logging
This section allows us to configure the events that we want to capture in the logs.
Let’s take a short overview of these events.
- OnError: It logs an entry in the logs, in case of any error
- OnExecStatusChanged: If the status of a task changes to resumed or suspended, it logs an event in the error log
- OnInformation: It writes the informational messages such as validation and execution of the task
- OnPostExecute: It handles the post-execution events. We can also execute the post package execution task in this
- OnPostValidate: once the task validation finishes, it writes an entry in the logs
- OnPreExecute: Before the execution of a task, it logs an entry in the SSIS logs. You can also use this to configure the pre-execution task in a package such as truncating tables
- OnPreValidate: Before a task validation starts, it writes an entry in the logs
- OnProgress: It writes the progress of tasks such as percent execution in the event logs
- OnQueryCancel: It handles the query cancel events and writes in the event logs
- OnTaskFailed: In case of any task failure in the SSIS package, it writes an entry in the SSIS log
- OnVariableValueChanged: if the value of variable changes inside the package, it writes an entry in package logs
- OnWarning: If the package execution raises a warning message, it logs an entry
- Diagnostic: It writes an entry to provide diagnostic information for the SSIS package
- DiagnosticEx: It provides additional information from the Diagnostic event to troubleshoot issues further
Click on the Advanced tab on the bottom page of the details page.
You get an additional option, and it allows you to select the additional columns to capture in the SSIS logs.
The SSIS log provider for Text files for SSIS package logging
Let’s configure the SSIS package for the OnError event and text file using the SSIS log provider for Text files:
Select SSIS Log provider for Text Files from the drop-down and click on Add.
In the Configuration column (number 3), click on New Connection.
In the File Connection Manager Editor, specify the file and usage type as Create File. If you have an existing file, you can use usage type as an Existing file.
Click Ok, and you can see the file name in the configuration column.
Put a tick in the checkbox near to the Name column and go to details.
We want to capture only the error events in the SSIS logs, therefore, select the OnError event message.
Click Ok and Execute the SSIS package. The package is failed again with the same error message.
Let’s go to the path in which we created the SSIS log flat-file and open SSISEventlog.txt file.
The SSIS log provider for SQL Server in SSIS Package logging
We can configure multiple SSIS log providers in the same package. Let’s add a provider to log information in the SQL tables.
Right-click on the control flow area and go to logging again. Select SSIS Log Provider for SQL Server from the drop-down list and click Add. You can use an existing SSIS package connection or use a new connection for SQL Server. Let’s use the existing connection only.
In SQL Server logs also, we want to capture the error only. Click on the details and verify that event logging is set to OnError.
Rerun the SSIS package. You can view the error message in both the flat file destination and the SQL Server database.
You can find the dbo.sysssislog table in the system tables of the database that you have configured for the SSIS package logging.
Execute the following query to select records from this table.
|
1 2 |
SELECT * FROM [AutoGrowthTest].[dbo].[sysssislog] |
You get four rows in the output corresponding with the following events.
- PackageStart
- OnError
- PackageEnd
The SSIS log provider for XML files for SSIS package logging
XML is also a popular method to store the SSIS logs. Let’s configure XML files using the SSIS log provider for XML files and specify the file name and path in the configuration column.
In the Details page, let’s capture more details by selecting OnError, OnInformation and onProgress messages.
Rerun the package and open the XML file in a browser to view the SSIS logs. You can see detailed information in the SSIS logs.
We do not see other events in this XML logs because the SSIS package did not execute any other tasks or made progress.
Capture additional details in the SSIS package logging
Let’s open the SSIS package configured in the article SSIS Conditional Split Transformation overview and configure the SSIS logs in an XML format and for the events OnError, OnInformation and onProgress messages.
The SSIS Package is successful this time. It should not capture the error details however additional details must be there in the log files.
Let’s view the output captured in the XML log file.
We can see additional information captured in the logs this time. It validates each task and logs entry for that.
Entry for the preparation of the executing phase in the SSIS logs.
The Final Commit of the data process is also logged in the SSIS logs.
You can see Post Execute events, clean up phase and End of execution events as well in the SSIS events.
Conclusion
In this article, we explored the SSIS package logging to capture the useful information, key events, failure messages in the SSIS package execution for multiple provider locations such as flat files, XML files, SQL Server, event viewer, profiler trace. It is a good practice to configure the SSIS logging for package execution. Stay tuned to understand other methods for logging in my upcoming article.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023