Maintenance Plan SSIS Packages fails on the exported server

Question

 

Hi All,

 

I have created a maintenance plan with package configurations. Exported that to a server and imported there. But, when I open the maintenance plan (either in the Management Studio or the Business Inteligence Development Studio), and try to edit, I get the below error.

 

Collection cannot be null. Parameter name: c (mscorlib)

 

I checked the connection string and other parameters and everything is fine. When I execute the plan too, this error is thrown.

 

I traced this to be related to some post SP2 hotfixes (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1456524&SiteID=17), but is there a work around this problem? I need to get this work urgently and so cannot search for the correct hotfix(es).

 

Can anyone please help here?

 

Thanks a lot,

Manoj Deshpande.

Answer 1

Anyone please?

 

Thanks.

Answer 2

I even updated the destination server to the hotfix level of the source (where the maintenance plan was created) and again tried to import it. BUT the same error again poped up!! Is there a solution for this??

 

Thanks.

Answer 3

Reponses from our internal BI/SQL Server team:

• We have to see the SSIS package that the Maintenance Plan Wizard created. Some times I have seen this kind of errors coming from Script task when you export from 32 bit platform to 64 bit and vice versa. But Script Task is usually not part maintenance plan. So if he can show us what it is then we can help.

 

• Since this is a problem when moving to another machine, and since configurations are being used, my first suspicion would be that the configuration on the destination machine is in some way different than the source machine (possibly incomplete or invalid). I have seen odd errors that were configuration related. I would try saving off the destination configuration (xml files? Database table?) and then copy the configuration from the source server to the destination and see if that eliminates the error.

 

• Also, it would be helpful if he listed the exact task considering that there are 11 Maintenance Tasks, but I am guess that he is using the Backup Database task. Regarding the configuration, my understanding is that configurations are used during run time but when trying to design, it will use the connection as you have it setup in the project. I would ensure that the connection is clearly defined in the dev project and not defined as LOCAL. He could be backing up a set of objects that do not exist on the new server and therefore without the clear definition during design, it cannot find the objects and therefore throwing the collection error.

-=Steve (www.rdabiblog.com)

Answer 4

Hi Steve! Never knew you were here ! :-) ..

As I said, It was the DestinationCreationType property of this task. I toggled between Auto and Manual; and when it was set to Manual, it threw this error. Otherwise it could open the properties of the task, and not throw the error.

But another issue now is that, if I import this package to a server, and create a job and run it (or even run the SSIS package alone), though it creates a back up of the database, but fails to finish successfully. Gives this 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

It probably fails to write a log of execution.. But I am not able to find an error in this sp - sp_maintplan_open_logentry, which it runs and throws the above error:

CREATE PROCEDURE sp_maintplan_open_logentry

@plan_id       UNIQUEIDENTIFIER,

@subplan_id       UNIQUEIDENTIFIER,

@start_time       DATETIME            = NULL,

@task_detail_id  UNIQUEIDENTIFIER    = NULL OUTPUT

AS

BEGIN

IF (@start_time IS NULL)

BEGIN

SELECT @start_time = GETDATE()

END

SELECT @task_detail_id = NEWID()

INSERT INTO msdb.dbo.sysmaintplan_log(task_detail_id, plan_id, subplan_id, start_time)

VALUES(@task_detail_id, @plan_id, @subplan_id, @start_time)

RETURN (@@ERROR)

END

The command it finally runs is this:

DECLARE @Guid UNIQUEIDENTIFIER

EXECUTE msdb..sp_maintplan_open_logentry

'{74485280-D50F-4C7A-BCCA-1C58DFD5C7FB}',

'{D61892B1-D122-4751-8DC4-33F739195545}',NULL, @Guid OUTPUT

Which throws the error

So, basically, it is now successful in doing what it is primarily supposed to (creating a backup of the database), but fails on this step.

Thanks a lot,
Manoj Deshpande.

Answer 5

 manoj,

have you tried using a t-sql TRY...CATCH block to trap the error? http://msdn.microsoft.com/en-us/library/ms175976.aspx

hth

---

MCAD ASP.NET / C# / SQL Server 2005

Answer 6

Hi Douglas,

All these queries and SPROCs I gave are with the SQL Server 2005 product, so I ideally should not make any changes, as these are "correct" :-)..

However, I found the cause of the issue and a solution also.

Looks like that error is because of this. I had imported the maintenance plan (SSIS package) into the destination server. But somehow, this entry was not added to the table 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 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 sysmaintplan_log, which has a FOREIGN KEY relation with the table sysmaintplan_subplans. Now, since the plan information itself is not available in this table, the log insertion will also fail.

 

I added an entry manually into sysmaintplan_subplans for this package and the job started to finish successfully.

 

So, inconsistencies see:

 

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

Any ideas as to whether I am missing something, or why these discrepancies exist?

Thanks,
Manoj Deshpande.

Answer 7

The initial problem was resolved using the steps described here:

http://bisqlserver.blogspot.com/2008/12/maintenance-plan-ssis-package-fails-on.html


Thanks!

-=Steve

Answer 8

Steve.. Smart! :-)... I had to discover and solve it before.. wish your post was available then... :-D... thanks anyway for putting it up there...

Manoj Deshpande.