posted on Tuesday, December 27, 2005 10:46 AM
What Log Provider Type is Best in SSIS?
SSIS has the ability to do very detailed logging that puts DTS Package Logs to shame. In the past, you would have to had to write customized logging routines to do what SSIS does out-of-the-box. SSIS offers a number of logging providers that you can select by going to SSIS menu and selecting Logging. You must then chose what type of provider you wish to use for logging. Which provider you chose depends on your goals:
- SQL Server Profiler create a profiler trace file - This option gives you some interesting ties in with Profiler if you're examining performance issues like long-running packages. With this seamless integration, you can quickly tie the log to a System Monitor trace to see performance counters.
- SQL Server Table writes events to a SQL Server table. - This option is my personal favorite since it allows for rich reporting using Reporting Services or the reporting tool of your choice. This is a must-have for most environments that need operational reports in real-time. I like to create reports against the table that refresh so you can see all the running packages and their progress.
- Windows Event Log writes each event to the Windows Application log - What makes this choice a nice one is it creates hooks for monitoring products like Tivoli or Microsoft Operations Manager (MOM) to use for alerts.
- XML File format writes each event to a structured XML file - This can also be used for reporting on a website if you use an XSLT file for formatting. It works well if you need to share the information with a 3rd party especially.
- Text file writes to a simple flat file - This option is the easiest to configure and is the most understood by traditional tech-heads.
What's especially nice about the new logging mechanisms is you don't have to chose just one. You can use for example the table for relational reporting and the Windows Event log for operational monitoring tools.
-- Brian Knight