locked
Managing Project/Package History RRS feed

  • Question

  • I have a situation where a package is scheduled to execute fairly regularly. At a minimum, it executes at least every minute. I want to manage the history of the package and possibly the project as a whole to retain no more than 14 days worth of history. In looking into the stored procedure [internal].[cleanup_server_retention_window], there doesn't seem to be a way to pass in a project or package parameter.  However, it looks like I may be able to replicate the procedure and have it do what I want if I can figure out where the Object ID is coming from.

    Any suggestions on how an easier way to manage the SSIS history for a specific project and/or package?  If not, does anyone know what table the Object ID column in the [internal].[operations] table should be referencing?

    Monday, October 30, 2017 3:46 PM

Answers

All replies

  • Hi JIMBO,

    Not sure why you want to replicate it. I understood only a select package history needs to be retained beyond the defaults.

    So instead you can leverage a query written by David Mauri to get a package execution history https://github.com/yorek/ssis-queries/blob/master/ssis-execution-package-history.sql

    And put into your status/log table then purge as often as you need.


    Arthur

    MyBlog


    Twitter

    Monday, October 30, 2017 6:06 PM
  • Thanks ArthurZ.

    From your comment, there doesn't seem to be an out-of-the-box feature that allows you to prune the history for a single project and/or package.  The workaround that you are suggesting is to store the history in a custom user defined table and prune that as necessary, correct?

    Wednesday, November 1, 2017 2:22 PM
  • Prune no, the retention time is adjustable 

    Yes, roll your own implementation on top.

    This way you are protected from touching system objects directly, which is a bad idea.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by J I M B O Wednesday, November 1, 2017 3:55 PM
    Wednesday, November 1, 2017 2:51 PM