Logging in SSIS : Usage of Log Reports and Log Provider selection

As per MSDN BOL, in SQL Server 2005, SSIS can log information about the package’s runtime to any of the five log providers: text file, SQL Profile trace, SQL Server table, XML file or a Windows Event log. You can also write to two different providers if you wish to audit in a multitude of ways. If none of these logging providers fit your appropriate requirements, you always have the option of building a custom logging provider.

The most common log provider to use is the SQL Server table provider. This is because you can then write Reporting Services Reports that can be viewed or subscribed to see a package’s status. If you select this provider, you must select which Connection Manager you wish to write to and then the events you wish to log (shown below). You can log each time a warning or error occurs or any information message.

Inside the database of your choosing, a sysdtslog90 table will be created. The sysdtslog90 table can grow rather large so you will need to schedule something to eventually archive the table. The 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.

But in practice, I have seen that the most favorite source of logging remains an XML file. Firstly no one gets interested in logging to a database, as it requires connecting to server and all the related administration and security related issues involved with it. Secondly, thou surprising, in development projects I have seen absolutely no projects where logging is taken so seriously that SSRS reports are developed to view the performance data. Actually, theoretically speaking, it should be done but in practice it's not.

XML based logging is also not a bad option at all. A simple XSLT can be developed for the XML document in which all the execution related data is being logged, and the log information can be seen in the format of choice like HTML for example. This saves hits on the server, requires absolutely low resources, minimal efforts to get a proper GUI for viewing the logged information in a report based format and generally security is not a concern for viewing of log reports as there's nothing sensitive in it. Also log reports are generally used either during debugging or performance tuning in development or production environments.

In theory, theory and practice are the same; but in practice they are not !