Remove old package versions

Question

Remove old package versions

philcart

SSC-Forever

Points: 47794
More actions
November 25, 2002 at 11:37 pm

#158390

Hi All
Just wondering if anyone has had a go at programmatically, either via T-SQL or ActiveXScript, removing old versions from DTS packages?
We have about 40+ DTS packages at various stages of development. It's a bit of a pain to have to manually re-visit each package and delete the old versions
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

This is what I use to clean out all old versions, however I do suggest even thou I trust it, always make a backup of the msdb database before you do just in case you hit one you didn't mean to. You need to put it in the msdb database.

CREATE PROCEDURE ip_RemoveOldDTSVersions

AS

delete

sysdtspackages

where

versionid in (

select

versionid

from

sysdtspackages

inner join

(

select

[id] mxid,

max(createdate) as mxcd

from

sysdtspackages

group by

[id]

) as MaxCD

on

[id] = mxid and

createdate != mxcd

)

go

Edited by - antares686 on 11/26/2002 04:17:30 AM

philcart SSC-Forever Points: 47794 More actions · Answer 2

Thanks Antares. Initial test performed great.

I'll link in the DTS logging tables so I can remove the old log records as well.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

Antares686 SSC Guru Points: 125444 More actions · Answer 3

DOuble check but I believe there is an SP for that built into msdb. No database online here right now so I cannot double check.

philcart SSC-Forever Points: 47794 More actions · Answer 4

quote:
DOuble check but I believe there is an SP for that built into msdb. No database online here right now so I cannot double check.

Yes, there's 4 of them sp_dump_dtslog_all, sp_dump_dtspackagelog, sp_dump_dtssteplog and sp_dump_dtstasklog.

I'll use the resultset from your query to feed the last three procedures.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface