SSIS Logging from within Stored Procedures?

  • Dear all,

    I am new to SSIS 2012 and I really like the default logging features.

    As many, I use a mixture of Packages and Stored Procedures and I would like to be able to log into the SSIS logging framework directly from within my stored procedures.

    Since it looks like the SSISDB Catalog has a stored proc interface, I suppose it should just be a matter of firing the appropriate stored proc with the relevant identifier...

    Has someone done it who could give me some tips?

    Thanks

    Eric

  • What exactly do you want to log withing the stored procedures?

    Maybe you can execute them with Execute SQL Tasks?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Yes, I would most probably start the procedure with a Execute SQL Task, passing something identifying the execution of the package (for instance the SSIS Db execution_id if I could get access to it).

    The sort of information could be intermediate results within my stored procedure: how many this, how many that, what was some condition at execution time, etc...

    Anything that would add (business) value to my logging

  • You can start the executions of packages also through a stored procedure. You can dump the value of the SSIS catalog executionID to a parameter in the package and use that one in your stored procedure.

    I would log to a table outside the catalog though and join it to the regular logging when you need it. You have control over the schema, while the SSISDB can change in a new version without a warning, possibly breaking your code. And it saves you all the effort of finding out where to log in the catalog 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I must admit, I am tempted to have my own logging beside that of SSIS DB, precisely for the reasons you mention.

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

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