May 9, 2012 at 1:27 pm
I'm trying to execute a Maintenance Plan task on all my instances. I've been successful in executing an 'Execute SQL Task' to them all.
My steps:
Execute SQL Task - gets connect strings from a table, puts them in a variable of type Object (name: ConnectResultSet)
Foreach Loop - enumerates the above variable and shreds individual connect strings into a variable of type String (name: ConnectString)
Connection (name: DynamicConnection) - uses an expression of above connect string variable ('ConnectString')
Execute SQL Task (inside FEL) - uses 'DynamicConnection' to run statement against the instances
This runs fine if the task inside the FEL is a Control Flow item. When I replace with a Maintenance Plan task (Check Database Integrity), I'm unable to change the connection in the Check Database Integrity task to 'DynamicConnection'.
The above steps were crafted from lots of web searches, but all the examples I found are using an 'Execute SQL Task' in the FEL. I couldn't find anything trying to pass the connect strings to a Maintenance Plan task and am wondering if this is possible.
TIA.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 9, 2012 at 3:02 pm
Got it to accept the dynamic connection string using the above layout but didn't think past that. The Maintenance Plan tasks are asking for more than just a connection string...it needs to know which databases to perform its maintenance on, among other things. I'm researching whether I can pass in those values.
If anyone has done this I'd love to hear about your experience.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply