Rajendra Gupta
View the execution status of SSIS package

Overview of SSIS Package Logging

August 23, 2019 by

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

Create a new SSIS package

Drag an Execute SQL Task in the Control Flow.

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.

Configure an Execute SQL Task

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.

Configure OLE DB Connection Manager

In the SQL Statement, specify the script you want to execute.

Specify SQL Task Editor

Click Ok and configuration is now completed for the Execute SQL Task.

Execute SQL Task after the configuration

In the Solution Explorer, Right-click on the SSIS package and click on Execute.

Execute package

The Red-Cross icon on the execute SQL Task shows that the package execution failed.

Failed symbol for the task

Click on the Progress tab for the detailed error message. By looking at the following screenshot, we can identify the error message.

Progress tab for the detailed 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.

Configure a SSIS package logging

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’.

Enable logging in the tree view

Put a tick in the project checkbox and it enables the logging for this SSIS package.

Put a tick in the project checkbox to enable logging

On the right-hand side, we can see two tabs for configuration.

Provider logs and details for configuration

Provider and Logs in the SSIS package logging

In this tab, we select the SSIS logging provider type and their configurations.

Provider and Logs in the SSIS package logging

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.

Details section in the package logging

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.

Click on Advanced page for details section

You get an additional option, and it allows you to select the additional columns to capture in the SSIS logs.

Specify columns to capture details

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.

The SSIS log provider for Text files for SSIS package logging

In the Configuration column (number 3), click on New Connection.

Create a 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.

File Connection Manager editor

Click Ok, and you can see the file name in the configuration column.

file name in the connnection editor

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.

Configure OnError event to capture failed error 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.

Failed message in the text log 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.

The SSIS log provider for SQL Server in SSIS Package logging

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.

Verify the OnError event message

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.

dbo.sysssislog log table

Execute the following query to select records from this table.

You get four rows in the output corresponding with the following events.

  • PackageStart
  • OnError
  • PackageEnd

View the logging messages in the dbo.sysssislog table

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.

The SSIS log provider for XML files for SSIS logging

In the Details page, let’s capture more details by selecting OnError, OnInformation and onProgress messages.

Configure additional events in the SSIS logging

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.

open the XML file in a browser to view 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.

View the execution status of SSIS package

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.

Validation Phase

Entry for the preparation of the executing phase in the SSIS logs.

Prepare for the execution phase

The Final Commit of the data process is also logged in the SSIS logs.

The final commit phase

You can see Post Execute events, clean up phase and End of execution events as well in the SSIS events.

Post execute phase SSIS

End of package execution

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
444 Views