Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)? [duplicate]

Question

I am having dozens of MSSQL Servers 2012 R2 on Windows Server 2008 R2 with similar setup, configuration (in virtual machines) in production. Neither has SSIS installed.

I have to similarly maintain (migrate, restore) the same maintenance plans on all of dozens servers

Any way to import-export-migrate-save-restore maintenance plans without having SQL Server Integration Service (SSIS) and Data Tools (BIDS) installed (and running)? Though I can stage a separate server machine for them

Saw the answer to "Running an SSIS package without the SSIS service installed" but still uncertain

Answer 1

If you have a working maintenance plan, which is really just an SSIS package, you can export that .dtsx file out of the database and redeploy to the next one.

For 2008/2012, those packages are stored in msdb.dbo.sysssispackages which you can verify with a query

;
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';

You will then want to pipe that information to repeated dtutil calls much as I describe on this SSIS Package Extract From msdb. Instead of the /file output though, you'd want to make it /destserver thatdatabase /copy SQL;PkgName See the answer over on https://stackoverflow.com/questions/7603021/updating-deployed-ssis-package/7605044#7605044

As noted in the comments, if you used package level encryption, you will need to add a parameter for that as well /encrypt file;"foo.dtsx";3;<somepass> This is presented as is because I don't advocate package passwords, nor have I tested it.