Post reply

Logging in SSIS

  • June 27, 2007 at 7:31 am

    #117580

    As part of the learning curve in SSIS, logging appears to be a big gotcha...

    1.  How do you read the log files (text) that are created from an SSIS package?  A text editor does not render them properly.  I want to be able to email the log files to administrators each time the job runs in order to troubleshoot.  More importantly, I want to be able to READ the logs.

    2.  If we decide to move logging to the SQL Server, will SSIS create a log table for us?  Probably not!!!  If not, what is the table structure for logging? 

    3.  In OSQL, we could implement a logging file to capture output from execution of an SQL script.  Is there a similar command line mechanism we can use in SQL Server 2005 and point to via Execute Process Task?  If so, how are the Task Parameters supposed to be set up?

    4.  How come Microsoft pushes logging infrastructure heavy lifting onto the developers?  Seems to me this should be built into one of the system databases (Model?) and simply made available as a system table to tap into for whatever database you are using.

    Thanks folks for your help!!!

  • June 27, 2007 at 8:07 am

    #715547

    SQL logging is by far the most useful as it is easy to render how you want.

    All logging is "raw" and not really meant for the eye. Rather for some app to read.

    Use SQL and stick a report or something else onto of the table.

    Yes, SSIS does create the table, by default sysdts90log if memory serves but you can change this.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 27, 2007 at 8:31 am

    #715556

    Ah...I got this working.  Very slick.

    Oooops, Gotcha!!!!

    It does not log any of the following from Execute SQL Task...

    1.  output from PRINT commands

    2.  counts from select/insert/update/delete commands

    3.  result sets

    ...the stuff used for auditing successful execution of an SQL script

    Does anyone know how to extract this from an Execute SQL Task?  That is the crux of my logging wishlist.

     

     

  • June 28, 2007 at 2:51 am

    #715764

    How would you log results sets, counts etc? There is a line between logging process information and auditing.

    As for auditing, there is a template which will log insert, delete and row counts. it needs some setting up but works.

    http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

    SSIS logging does most of what is needed, for auditing purposes, the possibilities are endless but you have to manually wire them

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 28, 2007 at 8:17 am

    #715864

    Thanks Crispin,

    I dumped a copy of the link you provided.  I'll need to figure out how to make it re-usable for multiple SSIS projects.

    So much to learn before I can finish migrating my DBs over to SQL Server 2005. 

    Best Regards,

    Marty

  • June 28, 2007 at 8:24 am

    #715871

    You cannot use it for multiple projects per-se but create a template from it and use the template.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply