SSIS package to create maintenance plan fails silently


  • This is SQLServer 2008.

    Here's what I did:

    • Connect to SSIS on server A (from management studio on server B, click connect, choose integration services, choose server A)
    • Find required maintenance plan
    • Right click and export to file, save it in a file accessible to both servers
    • Edit file to change server name in connection string to server B
    • Connect to SSIS on server B (as above)
    • Import package
    • Find file exported above
    • Execute it

    There are no error messages, yet the maintenance plan doesn't get created on Server B. Am I missing something fundamental about what SSIS packages are about?

    Extra info:

    The plan consists of a shrink db step, several TSQL steps and the run an agent job step. In the .dtsx file I can see all of those.

    I chose the option "do not save sensitive data" but I'm not aware of any in the plan.

    Thursday, November 29, 2012 4:55 PM

All replies

  • Are you talking about the maintenance plan package created by the SQL Server Maintenance Wizard?

    The package supposed not to create them, execute rather.

    Arthur My Blog

    Thursday, November 29, 2012 8:59 PM
  • If I understand your problem, I believe that there is a little more to do.

    I suggest that you read this: How to: Create Multiserver Maintenance Plans

    Perhaps that will help you get it working.

    As an aside, not the question you asked, also read: Why you want to be restrictive with shrink of database files


    Thursday, November 29, 2012 9:12 PM
  • The multiserver scenario isn't appropriate for what I want. I want to just copy a maintenance plan from one server to another. Thanks for the suggestion though.
    Friday, November 30, 2012 10:25 AM
  • No, I didn't use the Wizard, I just chose "new maintenance plan" and created it using the tool you get when you choose that menu option.

    I didn't realise the package wasn't for creating them. So it can't be done I guess. I'll have to create a new one by hand.

    Friday, November 30, 2012 10:27 AM