SSIS package query

I like to think of this query as "what the hell do I have deployed where?" It uses a recursive CTE to build out information from the system tables on the SSIS packages deployed to the MSDB and any folder(s) they may be in.

SQL Server 2008/2008 R2

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT 
    F.FolderPath
,   P.PackageName
,   F.FolderPath + '\' + P.PackageName AS PackagePath
,   P.packageFormat
,   P.packageType
,   P.vermajor
,   P.verminor
,   P.verbuild
,   P.ownername
,   P.PackageId
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
-- uncomment this if you want to filter out the 
-- native Data Collector packages
-- WHERE
--     F.FolderPath <> '\Data Collector'

SQL Server 2005

[Edit 2012-06-21] As I am back in a 2005 environment, the updated query for 2005 packages is below

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.[dbo].[sysdtspackagefolders90] PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.[dbo].[sysdtspackagefolders90] PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.[dbo].[sysdtspackages90] P
)
SELECT 
    F.FolderPath
,   P.PackageName
,   F.FolderPath + '\' + P.PackageName AS PackagePath
,   P.packageFormat
,   P.packageType
,   P.vermajor
,   P.verminor
,   P.verbuild
,   P.ownername
,   P.PackageId
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid