Free SQL Help for Devs, DBAs, and the Curious

In the world of SQL Server Integration Services (SSIS), logging is paramount. It is the primary mechanism by which ETL processes can be audited and errors can be diagnosed. SQL Server 2019 provides robust and flexible logging options, allowing you to choose what to log and where to log it. This blog post will explore various SSIS package logging strategies and demonstrate how they can be implemented in SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse.

In SQL Server 2019, SSIS offers built-in logging through the "SSIS log provider for SQL Server" feature. The logs can be written directly to the sys.sysssislog system table in the MSDB database. Here is a simple example of how to query the SSIS log:

-- T-SQL code
SELECT * FROM msdb.dbo.sysssislog WHERE event = 'OnError';

MySQL 8.0 doesn't natively support SSIS, but you can use the SSIS log provider for ODBC. The logs can be written to a dedicated table in your MySQL database. Here's an example of how you might structure your table for logging:

-- MySQL code
CREATE TABLE ssis_log (
 id INT AUTO_INCREMENT PRIMARY KEY,
 event_type VARCHAR(50),
 event_message TEXT,
 event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

For DB2 11.5, you can use the SSIS log provider for OLE DB. Similar to MySQL, you could create a dedicated table in the DB2 database for logging. The following is a sample DB2 script for creating a logging table:

-- DB2 code
CREATE TABLE SSIS_LOG (
 ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
 EVENT_TYPE VARCHAR(50),
 EVENT_MESSAGE VARCHAR(1024),
 EVENT_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP,
 PRIMARY KEY (ID)
);

Azure SQL and Azure Synapse provide more sophisticated and cloud-centric logging options. They offer integration with Azure Monitor and Azure Log Analytics, giving you a centralized and powerful platform for monitoring and troubleshooting your SSIS packages. You can configure your SSIS packages to send logs to Azure Monitor Logs, and then query those logs using the Kusto Query Language (KQL).

Regardless of the technology you use, a well-implemented logging strategy is essential for maintaining and troubleshooting your SSIS packages. It provides a valuable audit trail and can be a lifesaver when something goes wrong. By leveraging the logging options available in SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse, you can ensure that you have the necessary visibility into your ETL processes.