Maintenance Plan SSIS Packages cause problems on the exported server

Most often - I would say always - we develop the solutions on the development server and they are then implemented on Production. No where I have seen this case that the solution is directly developed on the Production - might happen somewhere, but that's not the matter of discussion anyway :-)...

When it comes to Maintenance Plans on SQL Server 2005 (or 2008 - I am not yet sure), the plan is created as a SSIS Package. So, it needs to be made portable by adding package configurations to it. Now, once the SSIS package is ready with confgurations to be deployed on the Production, we "import" it there. Here, I have seen a couple of issues which I will elaborate.

      1. One, when the package is imported, it is available in the Managment --> Maintenance Plans node of the Object Explorer.

Right click on the Maintenance Plan and select "Edit". It opens up in the designer. On the maintenance task, right click, and edit.



Ideally, we should be able to edit it. Instead it sometimes throws this error -

To resole this, open the package in the SQL Server Business Intelligence Development Studio, and in the properties of the maintenance task (like the Backup or the ReIndex as in the above screenshot), check the value of "DestinationCreationType". make sure that it is set to "Auto" and NOT "Manual". Then you should not anymore see this error and should be able to modify the package as required.

      2. Another issue is that, when the package is imported on the target server, it should ideally create a job automatically. But it does not. So, we need to create the job manually (lets script this job out from the server where we developed the maintenance plan). Let's spare that, and address even more complexity of the issue. When we run this job, (or even run the SSIS package alone), though it creates a back up of the database (if the maintenance plan is about database backup), but fails to finish successfully. It gives this error, or a similar error:

Started: 8:23:25 PM

Error: 2008-12-16 20:23:26.85
Code: 0xC002F210
Source: {7C327821-52CE-4177-B79C-6CFD236FF95A} Execute SQL Task
Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER



EXECUTE msdb..sp_maintplan_open_logentry '{74485280-D50F-4C7A-BCCA-1C58DFD5C7FB}', '{D61892B1-D122-4751-8DC4-33F739195545}',NULL, @Guid OUTPUT


Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


End Error
Error: 2008-12-16 20:23:29.88
Code: 0xC0024104
Source: {81E5BDFE-7937-4766-8A9F-A04D2E80BD28}
Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 8:23:25 PM
Finished: 8:23:30 PM
Elapsed: 4.094 seconds 

So, basically, it is now successful in doing what it is primarily supposed to (creating a backup of the database - or whatever the maintenance plan was created for), but fails on this step.

In many cases, the error is because of this. When we had imported the maintenance plan (SSIS package) into the destination server, somehow, this entry was not added to the table msdb..sysmaintplan_subplans. I am not sure why this happens. Also, when the package was imported it did not create a job. So, this job id information was also not added to this table msdb..sysmaintplan_subplans. Basically to say that the new package information did not reach this table.

The SPROC sp_maintplan_open_logentry adds an execution log into the table msdb..sysmaintplan_log, which has a FOREIGN KEY relation with the table msdb..sysmaintplan_subplans. Now, since the plan information itself is not available in this table, the log insertion will also fail.

We should add an entry manually into msdb..sysmaintplan_subplans for this package with the job id that we created manually. And the job would start to finish successfully.

  So, inconsistencies seen:

• A job does not get created for the maintenance plan when it is imported.
• A new entry into the table msdb..sysmaintplan_subplans is not added when the package is imported. 

Thanks for reading and I hope it help you solve your problem - well, if you faced a similar one... :-)

~ Manoj Deshpande