Not knowing enough about SSIS, I devised another solution in which a central (management) server retrieves SQL error logs from managed servers and stores them in a central repository.

It will allow you to ignore informational messages or 'false positives' based on server name and/or the time they occur (a good thing if you know you always get message 'Msg 1234' between 2 and 4 AM, but want to know about it when it occurs at another time). This means you can focus on the out-of-the-ordinary messages.

If you like, you can have SQL mail you any remaining lines for inspection.

Outline:

- The process is designed to run each day and collect 24 hours of error log lines.

- Collection is controlled by the management server, which fires an SP to start the collection process.

- The management SP then passes control to an SP on the managed server (= linked server).

- The SP on the managed server retrieves all lines from the last error log and stores these in a local holding table. If ERRORLOG contains fewer than 24 hours (server reboot or log reinitialization), the SP will read older logs (ERRORLOG.1-6), until the collections spans 24 hours (or logs are exhausted..).

- The management SP then continues and copies the lines collected on the managed server to a holding table on the management server. In the process, a servername is added and simple deduplication is done (duplicates are not copied, but increase the 'count' column for that particular line, while preserving info on first occurrence and spid).

- Next, a cleanup is done on the holding table, by comparing all entries to an 'exclude table'. The exclude table contains the text of all entries you want to ignore and has options to specify a server name, a start time and an end time (ignore only if between start time and end time)

If you make a good job of defining your exclude table, my experience is that very few lines remain, and those are the ones you want to know about!

It is too much code post in a reply, so I will add the code as attachments.

First, apply these scripts to a management database on your management server:

cre_table_SQLErrorLog.txt

cre_table_SQLErrorLog_exclude.txt

cre_sp_Retrieve_Remote_Errorlog.txt

cre_sp_Cleanup_Errorlog_Holding_Table.txt

Check, adapt and execute the script of lines to be ignored:

pop_table_SQLErrorLog_exclude.txt

Next, apply this script to each managed server (will reside in msdb)

cre_sp_Dump_Errorlog.txt

If you have DTC running on each server (I do not..) you can avoid the SP + temp table on each managed server. You can then run everything from the management server and manipulate/insert the results from a remote exec of SYS.XP_READERRORLOG on the management server.

Please feel free to mail any improvements.