Post reply

Delete DTS package versions

  • February 20, 2008 at 7:04 am

    #120417

    Is there a way to delete the older versions of a DTS package via a script?

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • February 20, 2008 at 10:37 am

    #780308

    Yes. You would delete rows from msdb.dbo.sysdtspackagelog, either for a specific package or by age. I run this to delete logs that are older than 180 days:

    DELETE

    FROM sysdtspackagelog

    WHERE logdate <

    (SELECT DATEADD(day,-180,MAX(logdate))

    FROM sysdtspackagelog d

    WHERE id = d.id)

    Greg

  • February 20, 2008 at 10:48 am

    #780313

    Would you know why I cannot see the sysdtspackagelog table? If I run the following command:

    SELECT *

    FROM sysdtspackagelog

    I get a empty recordset back. I am using the SQL 2005 clients looking at a SQL 2000 server.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • February 20, 2008 at 3:13 pm

    #780428

    Are you running the query in the msdb database of the SQL 2000 instance?

    Greg

  • February 20, 2008 at 3:15 pm

    #780430

    Yes I am.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • February 20, 2008 at 3:34 pm

    #780437

    Doh! My mistake. I should have told you to look at sysdtspackages rather than sysdtspackagelog. Notice that a new row is written every time a new package version is saved. Just delete rows where the createdate is older than the latest date for each package.

    Greg

  • February 20, 2008 at 3:40 pm

    #780439

    Thanks alot that did it.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

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

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