By Craig Colangelo, Sr Consultant for PerformanceG2
Many of us don’t have the budget to implement elaborate logging schemes into our smaller SSIS projects. That being said, even the smallest amount of logging will pay for itself the first time something blows up. In my experience, some logging, no matter how simple, is better than none.
Logging allows you to better understand load errors, diagnose longer batch run times and flush our load inefficiencies – just to name a few key benefits. In SSIS, logging can be as intricate or simple as you would like. Using just system variables and event handlers, you can create a functional logging system. Writing key system variable output during select events like OnError, OnPreExecute and OnPostExecute at the package container level, gives you a good idea of what is going on in every piece of your package. A few useful system variables are PackageName, SourceName, ErrorCode, ErrorDescription, StartTime, ContainerStartTime and MachineName. Writing to a log table with datediff’s between getdate() and desired system time variable, gets you very useful run time semantics. Through in row count tasks, user variables and send mail at various steps, and you have got a solid logging scheme. Spending the extra time upfront to implement the right level of logging in even your smallest projects will, undoubtedly save you diagnosis time in the future.
