I think maintenance plans were a great improvement in SQL Server 2005, with a more visual designer and the ability of the DBA to chain together whatever tasks they wanted. Service Pack 2 brought some enhancements, and a few bugs, so I’d recommend that you apply SP3 to your servers and clients if you are using them.
I rarely see issues with building packages since it’s a fairly simple designer, but there are two places where I have seen people have trouble editing them.
- Version differences
- Outside editors
I’ll explain the basic issues I’ve seen to give you a few things to check if you can’t open a plan.
Version Differences - The designer for maintenance plans is a cut-down version of the SSIS editor. As a result, there are sometime changes or enhancements to this designer delivered in SPs or CUs. If you have created a package in one version of SSMS, often you cannot open that package in a lower version. So the first thing to check is that the server and/or designer client is at a higher version than the one that is failing.
Outside Editors – The designer is a version of the SSIS designer, which is a version of Visual Studio. However those products contain many more features, and while you can use them to edit your maintenance plans, you can introduce changes to the plan that the basic SSMS designer does not support. As a result, you can’t then open the package again in the SSMS designer if you’ve opened it in Visual Studio or BIDS and changed something.
There usually are error messages to clue you in to the issue, but these are the two things I’d check first if you have issues modifying maintenance plans in SSMS.