Sql-server – Generate a new Sub Plan/Sequence ID in SQL 2005/8 Business Intelligence Development Studio

sql serversql-server-2005ssis

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.:

enter image description here

However I can't change the ID of the sub-plan sequence, the ID is greyed out:

enter image description here

Neither BIDS for SQL Server 2005 or 2008(R2) allow me to alter this value.

Is there a way to achieve this?

Rationale:

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.

For example:

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.

Best Answer

Look at BIDSHelper. Fantastic, free tool everyone developing SSIS and probably SSAS should be using.

Reset GUIDs

"This feature resets the IDs for all tasks, connection managers, configurations, event handlers, variables, and the package ID itself. When you copy a package, objects in both packages end up with the same IDs. This Reset GUIDs feature ensures that the IDs in the current package are unique."

Related Question