Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSISDB. Custom Logging Level

Answered SSISDB. Custom Logging Level

  • Saturday, June 16, 2012 8:37 PM
     
     
    Sign In to Vote
    0

    Hi everybody,

    I wonder whether it is possible to change what events are being logged in Basic/Performance/Verbose logging levels. It would be interesting, for instance, to log the number of rows passed in dataflow(Verbose Logging), but without all other detailed messages logged in verbose mode which bring additional performance penalty and "pollute" the logging tables.

    Another interesting feature would be changing the logging level during execution. I'd like to log in more detail in the ChildPackage1 (more error-prone or more critical) using the Verbose setting and log with "Basic" log level in ChildPackage2 and ChildPackage3. Is this somehow achievable?

     

All Replies

  • Monday, June 18, 2012 2:37 AM
     
     
    Sign In to Vote
    0

    generally you can controll the SSIS logging or your custome loggings (Known as "SSIS management Framework") by using .NET code

    , In my case i can active/deactive the SSIS logging and event active certen events and etc....

    so you answer for the first line is Yes.

    so you answer for the 2nd line is Yes.

    Q: Another interesting feature would be changing the logging level during execution

    A: I havent tried that but i have a feeling that the answer is yes, you will have to overwride the child objects and take over the events of the package and on each event active/deactive the logging that you may need

    QUESTION FROM ME: why do you need to active /deactive (changing) certen loggings while executing the package

    Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

     
  • Monday, June 18, 2012 8:37 AM
     
     
    Sign In to Vote
    0

    Hi Nik, thanks for answering.

    Just to be clear: I'm using the new Project Deployment Model (available with SSIS 2012) and deploy the packages in the SSISDB. When executing these packages, I can set the logging level (None, Basic, Performance, Verbose. See http://www.mattmasson.com/index.php/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/ ). Each of these levels logs some specific events. I would like to change what is being logged with the 'Basic' setting. Is this possible? I'm not interested in rolling out my own logging solution like in SSIS 2008/R2 (configuring SSIS Log Providers in design time (BIDS) in each package), I want to use the SSISDB Catalog functionality.

    QUESTION FROM ME: why do you need to active /deactive (changing) certen loggings while executing the package

    I start the ETL-Process executing the master package. This master package calls child packages with Execute Package tasks. I'm interested in number of processed rows in the DataFlow of the DimProduct package, and I'm not interested in processed rows information for DimPromotion or DimCheckoutCounter. The reasoning is that DimProduct data comes from a source system with large amount of manual entry and almost no constraint checking, hence there are much more possibilities that something goes wrong. I'd like to have detailed information for execution of this package.

    This is just an example, I hope the idea is clear

     
  • Thursday, June 21, 2012 2:53 PM
     
     
    Sign In to Vote
    0

    According to Matt Masson it's not supported currently

    http://www.mattmasson.com/index.php/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/

    -------------------------

    Hi Matt,

    can I somehow change what events are being logged for some logging level? For instance, I'd like to combine 'Basic' logging and timings from the Performance logging. I'd like to use the SSISDB Catalog functionality, i.e. I don't want to roll out some custom logging infrastructure. Thanks

    Hi Andrej,

    Unfortunately we don't currently support this functionality, but I can see it being something we add in the future.

    -------------------------

    Hope this will be indeed supported in next service pack or release. Kind of silly restriction in the overall good logging solution

     
  • Thursday, June 21, 2012 3:45 PM
     
     
    Sign In to Vote
    0

    QUESTION FROM ME: why do you need to active /deactive (changing) certen loggings while executing the package

    I start the ETL-Process executing the master package. This master package calls child packages with Execute Package tasks. I'm interested in number of processed rows in the DataFlow of the DimProduct package, and I'm not interested in processed rows information for DimPromotion or DimCheckoutCounter. The reasoning is that DimProduct data comes from a source system with large amount of manual entry and almost no constraint checking, hence there are much more possibilities that something goes wrong. I'd like to have detailed information for execution of this package.

    This is just an example, I hope the idea is clear

    The way that you are using a MASTER and a CHILD package is perfect, an I love it, BUT the way you are calling your CHILD package you may be limited, specially when you say ...."I'm interested in number of processed rows in the DataFlow of " AND you are using "Execute Package tasks"

    What I do is I call the packages using a SCRIPT TASK, i have more control from 2 point of views

    1- I can add/set objects in SSIS on the fly while calling the package, a good example is setting extra seetings in the SSIS Loggings if i need it

    2- from a Object Oriented (OO) perspective, ie: Capturing the "PipelineRowSent" of a DFT, which i am assuming what you are looking for that does "Rows were provided to a data flow component as input". Using OO you are basically OVER riding (taking over) the SSIS events and objects to capture things like error messages and events and what you are looking for the number of rows within a DFT

    http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.dts/2007-05/msg00190.html

    Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

     
  • Thursday, June 21, 2012 4:52 PM
     
     Answered
    Sign In to Vote
    0

    Thanks for input, Nik, but as I've already said

    "I'm not interested in rolling out my own logging solution like in SSIS 2008/R2..., I want to use the SSISDB Catalog functionality."

    SSIS 2012 Logging (SSIDB Catalog) unfortunately does not support my scenario currently.

    • Marked As Answer by Utwigg Friday, June 29, 2012 8:22 AM
    •  
     
  • Thursday, June 21, 2012 5:00 PM
     
     
    Sign In to Vote
    0

    Correct me if i am wrong, you want to set the SSIS loggings to capture the number of rows in DFT? if so

    by doing what i had mentioned you are setting the SSIS Logging , Your are not rolling your own logging solution, basically you are setting the current SSIS logging.

    good luck

    Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

     
  • Thursday, June 21, 2012 5:01 PM
     
     
    Sign In to Vote
    0

    SSIS 2012 Logging (SSIDB Catalog) unfortunately does not support my scenario currently.

    yes you are right.

    Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

     
  • Friday, February 01, 2013 3:25 PM
    Moderator
     
     
    Sign In to Vote
    0

    Please read/vote for this Microsoft Connect suggestion:

    http://connect.microsoft.com/SQLServer/feedback/details/778224/ssis-2012-logging-levels-too-restricted

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter