How do I modify a Managment Plan to add conditions? I assume I use SSIS, but
do I build a new Management Plan in SSIS or do I modify an existing
management plan already in SQL? If I modify an existing plan how/where do I
open it?

Thanks

I assume you mean "Maintenance" plan. Yes, you can modify such in SSMS. connect to the database
engine. Check in the Management, Maintenance Plans folder. Right-click the plan and select "modify".

If you want to do this in VS, you would have to export it from msdb to a dtsx file and modify that
dtsx file in VS. Then import it back into msdb.
Show quoteHide quote

Yes Maintenance Plan - thank you.

I exported the plan from SQL to a dtsx file. Then created a new package in
SSIS, where I added an existing item, selecting the file I exported.

The package contained 2 Sequence Containers - one for a full backup & one
for a transaction log backup. Each of these containers had a reporting step
that ran afterwards.

I added a variable: MirrorRole type Int32

I modified each sequence container as follows:
Added a SQL Task to put the results into the MirrorRole variable. SQL
command:
Select cast(Mirroring_Role as int) as Mirroring_Role from
msdb.sys.database_mirroring where database_ID in (Select database_ID from
master.sys.databases where name = 'MyDatabaseName')
Note: the MirrorRole column is a TinyInt. SSIS would not convert/move this
to my variable defined as Int16 nor Int32, which was why I used the CAST
function.

Added a Sequence Container after the SQL Task, that runs when the MirrorRole
variable = 1

Moved the Backup Task into the Sequence Container

Moved the Reporting Task inside the original Sequence container & after the
new Container.


I was able to run each individual task in SSIS, but when I tried to run one
of the Sequence Containers it did nothing. Don't understand, so suggestions
if you have them.

I then deleted the Maintenance Plan & imported it from the SSIS file. I had
to re-add schedules to the different sub-plans.

When I ran the individual jobs, each ran fine, When I switched the role of
the database on the server to Mirrored, the jobs failed, because they were
mirrored.

I could not see any of my changes in the subplans after I imported the
package, so did they get dropped?

Did I import my changed package? (I thought so.)

Did I configure the plan incorrectly in SSIS?

Thanks in advance.


Show quoteHide quote

Generate SQL scripts in Management Studio
Utility to read transaction logs
CPU-bound or I/O bound?
Re: SQL Profiler 2005
can someone have idea on this perfmon result