arronchester
asked on
Query to return list of databases selected in a SQL 2005 maintenance plan?
Is there a SQL 2005 equivalent of the attached code to return the list of DBs in a maintenance plan?
I have heard this info may be embedded in the SSIS package and is not queryable.
Can anyone confirm?
We use LiteSpeed for our backups and have a stored proc on our SQL 2000 servers which queries a given maintenance plan name to return the list of databases that should be backed up.
This means when we change the list of databases in a maintenance plan, our proc automatically picks up the change and LiteSpeed backs up the correct databases.
Works fine, until we try it on SQL 2005 :(
Cheers, Arron
I have heard this info may be embedded in the SSIS package and is not queryable.
Can anyone confirm?
We use LiteSpeed for our backups and have a stored proc on our SQL 2000 servers which queries a given maintenance plan name to return the list of databases that should be backed up.
This means when we change the list of databases in a maintenance plan, our proc automatically picks up the change and LiteSpeed backs up the correct databases.
Works fine, until we try it on SQL 2005 :(
Cheers, Arron
select database_name from msdb..sysdbmaintplan_databases where plan_id = @planid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can anybody help? :D
ASKER
So is there a way to open the SSIS package in TSQL and get the list of databases selected in the plan?
I've written TSQL before to open DTS packages and get details of connections using the following procs:
sp_OACreate 'DTS.Package'...
sp_OAMethod @object, 'LoadFromSQLServer'...
sp_OAGetProperty...
Can anything like this be done to get info from an SSIS package?
Thanks