I have what is effectively a template SSIS package (well a maintenance plan actually) that is copied and used as the basis of new maintenance plans.
In this package there is a Sequence (or a sub-plan) containing a number of tasks.
I know I can change the package's ID in BIDS, i.e.:
However I can't change the ID of the sub-plan sequence, the ID is greyed out:
Neither BIDS for SQL Server 2005 or 2008(R2) allow me to alter this value.
Is there a way to achieve this?
To give you some background, these packages are automatically installed on a server using
dtutil. A script is then run to create a SQL Agent job for the package. Part of that script executes a MSDB system stored procedure called
msdb.dbo.sysmaintplan_subplans. This stored procedure is used to join up the dots between a job, a plan and a sub plan.
EXEC @ReturnCode = msdb.dbo.sp_maintplan_update_subplan @subplan_id = @thesubplanid, @plan_id = @theplanid, @name= @subplanname, @description = 'Created by Provisioner', @job_id = @jobid, @schedule_id = @jobscheduleid_out, @allow_create = 1
@plan_id' is the package ID and@subplan_id` is the sequence or sub-plan ID as shown in the screenshots above.
sysmaintplan_subplans adds a record to the table
msdb.dbo.sysmaintplan_subplan, however the primary key is the
subplan_id. This is why I need to be able to change the ID of the sub-plan in BIDS.