Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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

enter image description here

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

share|improve this question

marked as duplicate by Kin, Max Vernon, billinkc, RolandoMySQLDBA, Paul White Nov 15 '13 at 4:35

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

up vote 4 down vote accepted

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 http://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.

share|improve this answer
1  
Thanks. But on execution of dtutil /sourceserver localhost /SQL "\Maintenance Plans\MaintenancePlan_daily" /copy file;.\MaintenancePlan_daily.dtsx I am getting the error: "The package \Maintenance Plans\MaintenancePlan_daily can not be saved to the file system since its protection level is server storage. Use the encrypt action to change the protection level" And I cannot find how to pass through it –  Fulproof Nov 15 '13 at 9:16
    
Shall try the last answer from SSIS: Import Packages error –  Fulproof Nov 17 '13 at 11:51
    
I created a maintenance plan and it wasn't stored with server storage encryption. dtutil /sourceserver localhost\dev2012 /SQL "\Maintenance Plans\POC MaintenancePlan" /copy file;".\POC MaintenancePlan.dtsx" Any idea how I can force a maintenance plan to be encrypted to reproduce your error? –  billinkc Nov 18 '13 at 0:35
    
Wanted to ask a long time ago but I am not sure how to formulate and where to put my question. It is not me who who installed and administers the AD domain and Windows (Servers 2008 R2) in servers (I am also not DBA but a programmer). I guess it is something connected either with UAC or domain policies with which I cannot meddle or even view them. Also I cannot save any files upon editing in servers. When I need to change something, I change it on my local comp and copy with replacement them to servers –  Fulproof Nov 18 '13 at 1:58
    
Instead of /copy file;"foo.dtsx" you need /encrypt file;"foo.dtsx";3;<somepass> to get past the encryption issue. –  rainabba Nov 16 at 18:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.