Monday, February 1, 2010

SSIS-Logging

When working with designing and developing ETL processes, one of the important aspects that needs to factored 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 might 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 his table at he beginning and completion of each step. This could be tedious, since there could be lot of calls to the same stored procedure using multiple execute sql tasks. One a new SSIS package is being created, SSIS itself provides logging capabilities. The logging option can be seen by clicking anywhere on the design surface of the SSIS package. When you click on the logging option, the following window pops up, there will be SSIS package and the various tasks in SSIS package on the left. On the right hand side within the window you have two tabs Providers and Logs, Details. When you choose the Provider type there are different types available:

SSIS log provider for SQL Server,
SSIS log provider for Windows Event Log
SSIS log provider for XML Files
SSIS log provider for Text Files
SSIS log provider for Sql Server Profiler
For my project I choose the SSIS log provider for SQL Server, Once this is done check the box next to the option SSIS log provider for SQL Server, then  choose the database connection where the logging will happen, when SSIS package runs it creates a table called sysssislog. In the details tab, choose the Events that need to be tracked, look at the figure below for reference.

Once this is done, click the OK button, Logging is now enabled for the SSIS package. Once the package is executed, look for the table sysssislog in the System databases under the database which was chosen in the Configuration column in the Provider and Logs tab. The sysssislog table has columns called source (contains the name of each task which is executed), message which contains more detailed message about the task,event (which contains the event that were chosen to be audited). The table also has columns called starttime and endtime which indicates the start and end time of each task.
SELECT * FROM dbo.sysssislog
To summarise, using the Logging option in SSIS, one can audit SSIS packages very effectively.

11 comments:

  1. Hi Gurus,

    Please conform weather oracle-DAC is supporting SSIS tool for integration

    thanks

    ReplyDelete
  2. Thanks for sharing this... Incase someone looking for 3rd part ssis logging - monitoring solution here is the link

    SSIS Logging

    ReplyDelete
  3. please remove spelling mistakes for dumbs like me

    ReplyDelete
  4. Hi,
    @anonymous: I have done a spell check and removed the spelling mistakes except for the Keywords like SSIS,ETL and sql.
    Thank you for your feedback

    ReplyDelete
  5. hi ram,
    thanks for the article. it is good.
    i followed same steps. the sql works in which database:

    SELECT * FROM dbo.sysssislog

    ReplyDelete
  6. Hi Lakshmi,
    The table is available in the msdb database.

    Thank you

    ReplyDelete
  7. Thanks Ram simple and neat ......;)

    ReplyDelete
  8. Here is a good query you can use against dbo.sysssislog
    http://troywitthoeft.com/simple-ssis-package-monitoring-for-sql-server-2008/

    ReplyDelete
  9. Hello Ram,

    Can i rename dbo.sysssislog ?

    ReplyDelete