Log or trace of activities will help you to get detailed information on what's going wrong, that too very useful for ETL based processes. In this regard within SQL Server 2005 using SSIS you can enable such logging within the package's runtime...
SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)
Wed, Apr 9 2008
Filed under: Filed under: sql server, package, profiler, ssis, xml, error log, 2005, information, warning, logging, event viewer