Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
Oh this is sweet! –  Kev Feb 14 '12 at 14:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.