SQL Server 2005 logging providers - make best use for SSIS packages to diagnose the issues
Log or trace of activities will help you to get detailed information on what's going wrong, that too very useful for ETL based processes. In this regard within SQL Server 2005 using SSIS you can enable such logging within the package's runtime.
This will allow you to log the output to 5 providers, such as text file, SQL Profile trace, SQL Server table, XML file or a Windows Event log. Further you can take advantage of writing the same to 2 different providers, such as building custom logging provider. In this scenario the most common log provider to use is the SQL Server table provider, where you can simply integrate with your reporting system such as Reporting Services Reports that can be viewed or subscribed to see a package’s status. In this case you have to select this provider, you must select which Connection Manager you wish to write to and then the events you wish to log and based on your approach either you can log the warning or error or even all the package execution information, in this case make sure you have sufficient disk space availble where the log file is stored when you hundreds of SSIS packages to handle.
Internally the SQL engine retains this information under sysdtslog90 table that can easily grow large when you have such a multitude of packages, and its better to think about archiving the log information too for historic purpose. The reason being this table is full of operational data like when each task started and stopped and the success of each one. The most important column in the table is the message column that stores the verbose result of that step.
In this scenario the package logging can be enabled separately for each of the executables, such as tasks and containers, as well as the package itself, as you might expect, are components capable of generating and detecting events. Depending on your preferences, logging options are either shared or set independently, varying in the range of event types to be tracked or the level of details to record for each event type. The same applies to the format and destination of logs, which are determined by logging providers, assigned to each log. From the five specific providers mentioned above you can choose which one is suitable for you, more about them as follows:
-
SSIS log provider for Text files - the most straightforward option, redirecting entries to a comma-separated value (CSV) text file. In order to review them, you might want to consider importing the content of the file into an Excel (or other application capable of dealing with structured text files).
- SSIS log provider for SQL Profiler - stores logged entries in the format (as a .TRC file) that can be viewed in the SQL Server 2005 Profiler. This gives you the ability to correlate entries generated in response to monitored events with other types of data available via Profiler (such as stored procedure activities, performance or locking-related traces, etc.)
- SSIS log provider for SQL Server - loads event entries into a SQL Server database table called sysdtslog90 (with such columns as event, computer, operator, source, sourceid, executionid, starttime, endtime, datacode, databytes, and message, which match the elements of SSIS log schema), using the OLE DB connection settings (this connection gets associated with the provider at the time of its creation). This gives you the ability to extract logging information via T-SQL queries (for example, using Execute SQL Task).
- SSIS log provider for Windows Event Log - records event information in the Windows Application Log, which you can monitor (and configure to send alerts and take appropriate corrective actions) with a systems management software (such as Microsoft Operations Manager or any similar third party program capable of extracting this information). The entries point to SQLISPackage as their source and, depending on the content, might be labeled as Informational, Warning, or Error messages. In case the format and content of the event information created in this manner is not sufficient, you also have the option of redirecting SSIS log entries to the Windows Application Event log through a code invoked via a Script Task.
- SSIS log provider for XML files - dumps event entries into an XML-formatted file, which can be either viewed via an XML viewer of your choice, or displayed through a Web page. This approach also facilitates transferring relevant information to a third party system, which is not capable of working properly with the output from other logging providers.
Further read on from KBA How to interpret data that is logged by using a SQL Server 2005 & Custom Logging in SQL Server Integration Services Packages tip.