posted on Tuesday, December 27, 2005 10:46 AM by bknight

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

Comments

# re: What Log Provider Type is Best in SSIS?

Monday, February 13, 2006 12:12 PM by Vipul Shah
for the Text File option, can you overwrite a text file each time SSIS pacakge runs? I tried using "Create File" option when configuring Log Provider for Text file, but its still appending a log file each time I run the package. Am I missing something?

Thanks
vipul

# re: What Log Provider Type is Best in SSIS?

Wednesday, May 03, 2006 11:12 AM by MarkAx
I'd be very interested in an answer to vipul's question. I'd like to use package logging but I don't want to have to manage the log file, which looks like it could get very large, very quickly.

# re: What Log Provider Type is Best in SSIS?

Thursday, July 12, 2007 8:57 PM by Jason
does anyone have info on creating a custom log provider? I want to track the rows processed and duration of each task using a custom log provider. I'm surprised the out of box log providers don't track this info in an easy to read format. Thanks.

Jason

# re: What Log Provider Type is Best in SSIS?

Thursday, September 27, 2007 2:38 AM by Suman
Does anyone has an idea about the max. size of log file supported? Can the log be written to multiple text files?

# re: What Log Provider Type is Best in SSIS?

Thursday, March 13, 2008 9:23 AM by Charley
SSIS log provider for SQL Server table does not work when you run the package as an SQL Server Agent job.