Search code examples
sqlsql-serverdts

SQL Server 2000 Script to list all Meta Data Services DTS packages

I am doing an SSIS package that would go out to all our DB servers to gather information about them, and what is on them. And in this one case there is a SQL 2000 box with a bunch of DTS packages on there.

Now I can easily list the Local Packages using "exec sp_enum_dtspackages", but this doesn't list the Meta Data Services Packages.

I cannot find anything obvious online or in the database to help me with this, not really even then attaching a profiler to it.

So I was hoping someone might be able to help me with a script. I am looking for the name, description, owner and the create_date.

Thanks in advance.

Solution 
  • use msdb
go

select no.Name, si.Comments as Description, vai.CreateByUser as Owner, vai.VersionCreateTime as CreateDate
    from RTblNamedObj no
        inner join RtblSumInfo si
            on no.IntID = si.IntID
        inner join rtblVersionAdminInfo vai
            on no.IntID = vai.IntID