Package Meta Data from MSDB

A lot of times during release phases, there are instances where a package that is currently in release is not the package that is currently in production.  So how do you tease out what is in production if you don't have the ability to get the file directly via SQL Server management studio, but you DO have select access to the msdb and your packages are stored in MSDB?

The following code will grab pack folder information, and verbuild information from the SSIS meta data stored in MSDB for a specific package, and then display that information.  It contains the package name, the msdb folder, the version information, and other information as needed.  This code allows a user to quickly verify whether his expected build is in the MSDB, without running a package and using any kind of logging hints.

/*

Code originally taken, slightly modified from

Bill Fellows of http://billfellows.blogspot.com

http://billfellows.blogspot.com/search?q=sysssispackagefolders

This modification adds a packagename variable to make the code a bit more straight forward to use.

*/

DECLARE @packagename varchar(255)

set @packagename = 'Your package Name, can leave null for all packages'

;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.verbuild

,   P.ownername

,   P.packageFormat

,   P.packageType

,   P.vermajor

,   P.verminor

,   P.PackageId

FROM

    FOLDERS F

    INNER JOIN

        PACKAGES P

        ON P.folderid = F.folderid

        WHERE

        (

                  p.PackageName like '%' + @packagename + '%'

                  or

                  @packagename is null

            )

            and

     F.FolderPath <> '\Data Collector'

Related post:

http://themobilefieldbase.blogspot.com/2013/03/export-ssis-from-msdb-when-all-else.html