Migrating a Legacy Maintenance Plan?

Has anyone been able to migrate a Legacy Maintenance Plan to the Integration Services style?

I'm using the SQL 2005 RTM, right-clicking the Legacy Maintenance Plan, and selecting "Migrate..."

After a few seconds, it presents the error "Input string was not in the correct format. (mscorlib)"

The only thing that sticks out in SQL Profiler is this statement:
SELECT s.log_shipping AS [LogShipping]
FROM msdb.dbo.sysdbmaintplans AS s
WHERE (s.plan_name=N'MaintenancePlan1')

and this error message:
Invalid column name 'log_shipping'.

but these happened near the beginning of the trace/action.

To the best of my knowledge, there isn't anything special with these Maintenance Plans. The plans have four standard jobs (Optimizations, Integrity Checks, DB Backup, Txn Log Backup). The jobs all have one jobstep, which start with "EXEC master.dbo.xp_sqlmaint ..."What is the sku of sql server 2000 that you are migrating from? What is the sku of the sql server 2005 you are migrating to? Log shipping is present only in the enterprise version and dev sku of sql server 2005.

A workaround could be to write a t-sql script to add a column called LogShipping to the table msdb.dbo.sysdbmaintplans and fill it up with 0.

After doing that, you can try to migrate. I think it should work.

Do let us know if that helps

Hope that helps
-nimesh|||A small correction in the suggested work around. The colum name should be log_shipping.

-nimesh|||I'm upgrading a SQL 2000 Enterprise to SQL 2005 (MSDN) Enterprise as a proof-of-concept.

Thanks for the suggestion. I tried this.

I'm still getting the error message "Input string was not in a correct format (mscorlib)" when I migrate a Legacy Maintenance Plan.

I don't understand as the SQL Profiler scripts are clean. Since this appears to be a .NET issue, I don't know how I can trace it.|||

I figured it out!

The Optimizations job had a trailing line-break character in the job step. Once removed, the plan migrated fine!

The migration scripts do not like line-breaks or comments of any kind.

Now that it migrated, I still have more questions...

1. Why didn't the legacy maintenace plans disable? Now I have a SQLMaint and SSIS version of the same job active and running at the same times. The migration could have asked me if I'd like to disable or delete the older jobs.

2. Legacy maintenance plans cannot be deleted from the SSMS GUI. (Refresh after you try.) One must use a combination of sp_delete_maintenance_plan and deleting the associated jobs.

|||(I also removed the log_shipping column from earlier and still had success migrating the plan)