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

SQL Agent job calling SSIS package never completes!

While I was working on SSIS Packages for a project, and was trying to schedule it with a SQL Agent job, I came across a situation.

I was using two SSIS Packages. I configured each package to be called from the SQL Agent job as a separate step, as they were to do different tasks – one dependent on the other. The first package was called as part of the second step in the job, and the second was called in the 5th step. I noticed that the job would get past the second step, third and the fourth. But on the fifth, it kept running – indefinitely.

More strange was the fact that the SSIS Package when run from the SQL Server Business Intelligence Development Studio, was running successfully (and finished, which is of more importance). But the issue was only when the package was scheduled in a SQL Agent Job.

It didn’t end there. I almost did a research on the net and wished to try something I found. It said, restart the server! The great restart! I did that and phew! It completed when I run it. I did not stop there. I tried to run once more and as expected it never completed. I restarted the SQL Server Service – it also restarted the SQL Agent Service. Then again, when I run the job, it completed. I tried once more, it never completed!! At this point I was frustrated. After a long research, I tried putting a hosts file entry. Details below.

The SSIS Package – in turn the Server was trying to reach the website http://crl.microsoft.com to call the CryptoAPI function to verify the certificates that are assigned to the SQL Server assembly files. The function CryptoAPI would check Certificate Revocation List on that site. This surely requires that the Server in on the internet. As it usually happens, the servers do not have open connection to the internet, and hence it was never completing, probably waiting to get connected.
As a solution, I put this website to point to 127.0.0.1 (self) in the hosts file – and guess what, the job started to complete successfully thereafter. Here is a screenshot of the hosts file on the server –

It is important to note that the server I was working on is a 32-bit server.

I sincerely hope this post saves hours of time for someone!

~ Manoj Deshpande.