SSIS Package Extract from MSDB

A question came up on StackOverflow of how someone can retrieve their packages from the MSDB. In this case, their local development went south and they only had the packages in production. Let this be an object lesson in using version control.

To extract packages from the MSDB, you must first identify where in the msdb they exist. For that, you can query sysssispackagefolders and sysssispackages or you can just use my query SSIS Package Query

Armed with that query, the column of interest is the PackagePath column. Couple that with dtutil and you have an extract-o-matic for package recovery.

The base form of an extract from MSDB on localhost to the current folder in the file system would look like.

dtutil /sourceserver localhost /SQL "Package" /copy file;.\Package.dtsx

Extract-o-matic

Run this query in Text mode (ctrl-T) This query generates a series of dtutil calls which in turn extracts SSIS packages from a server.

;
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 
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename') 
    -- if you need to really make this generic
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;.\' + P.PackageName +'.dtsx' AS cmd
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'

For me, that generated the following code.

dtutil /sourceserver localhost /SQL "\Package" /copy file;.\Package.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\PerfCountersCollect" /copy file;.\PerfCountersCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\PerfCountersUpload" /copy file;.\PerfCountersUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\QueryActivityCollect" /copy file;.\QueryActivityCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\QueryActivityUpload" /copy file;.\QueryActivityUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\SqlTraceCollect" /copy file;.\SqlTraceCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\SqlTraceUpload" /copy file;.\SqlTraceUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\TSQLQueryCollect" /copy file;.\TSQLQueryCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\TSQLQueryUpload" /copy file;.\TSQLQueryUpload.dtsx

Hooray, my bacon is saved and now I can put my packages into version control like a good developer.