Best way of logging in SSIS

Question

There are 5 different types of logging in SSIS

1. Event Log
2. Text File
3. XML File
4. SQL Server
5. SQL Server Profiler

I am in a production environment where developers do not have access to production systems.

Which logging method should be my poison of choice, and why?

Answer 1

Personally I prefer logging to SQL Server.

I think this is because it puts the data in a form which I can immediately access and process. For example, I can then slice and dice the data, export it to another server, setup agent jobs to monitor the logs and email alerts etc.

Answer 2

If you're not going to have access to the production server, then SQL Server logging is your best bet by far. You'll have plenty of ways of viewing the logged information, for example via custom SSRS reports or web pages, or direct access to the tables if your DBA allows it. Also, the logs will be easier to search and filter when in a table.

Answer 3

Have you looked at BI xPress from Pragmaticworks. They have serious auditing feature for SSIS

SSIS Logging And Auditing, Notification, Deployment using BI xPress