Wednesday, November 17, 2010

SSIS Logging

When working with designing and developing ETL processes, one of the important aspects that need to factor in is the auditing of the ETL process. It is very important to keep track of the flow of process with in ETL. While working with SSIS, it is important to design auditing feature for a SSIS package. There are several ways of auditing SSIS packages, one is use to the SQL Jobs history to figure out if there was an error in the SSIS job step and then troubleshoot the problem. This may not effective way to audit, the other option would be is to create an audit table with in a SQL server database and keep inserting records into the table. One a new SSIS package is being created, SSIS itself provides logging capabilities.

E.g.:- Consider the following scenario: a package has been developed in the development environment, tested it in test environment and finally deployed it onto the production server. In the beginning everything was fine, with the package running as expected. Sometime later the package execution failed each time upon being run. In this situation there is commonly uncertainty regarding what is causing the package to fail. Hence, logging is very useful. So what is event logging is and how does it help understand problems encountered during runtime.
Integration Services includes logging features that write log entries when run-time events occur and can also write custom messages. We use the logging to log the information about the event happening at the run-time for auditing and troubleshooting purpose.

SSIS package logging can log the information on one or more different selected log providers as mentioned below:

• SSIS log provider for Text files –
logs the information into a text file, during configuration the location and file name need to be specified.

• SSIS log provider for Windows Event Log – logs the information into the Windows Log Event under Application node and SQLISPackage90/ SQLISPackage100 as source. Writes each event to the Windows Application log - What makes this choice a nice one is it creates hooks for monitoring products like Microsoft Operations Manager (MOM) to use for alerts. i.e. In windows 7 StartMenu --> Run --> Event Viewer --> Windows Logs --> Application

SSIS log provider for XML files – logs the information into the XML file, of which the location and file name are specified during configuration.

SSIS log provider for SQL Profiler – creates a *.trc file which can be opened in SQL Profiler to analyses further. 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

SSIS log provider for SQL Server – logs the information into sysdtslog90/ sysssislog table of msdb database that requires a connection manager to be provided during configuration. Creates a table Points to the location where the connection manager points to the database in SysTables-->Sysssislog table.
Example: To enable Logging, right click on the package and select logging or or Go to the menu SSIS and select logging: A logging configuration window will pop-up.

Containers Tree view on Left-side – displays the hierarchical view of a package and all its executable (containers and tasks). Select the executable to enable for logging. Providers and Log Tab - Select one or more different log providers from the combo box and click the Add button on the right as shown below, two log providers have been selected; one to write to text file and the other to write to Windows Event Log. You can configure the file location.

Details Tab – This tab displays all the events of the selected executable, select which events to log. For example: below OnError and OnTaskFailed event have been selected at the package level. After the package has run the result of logging can be seen.