It's the Database!!! (3/19/2013)
I added the expression with no luck. It still puts master in serveral times and not the other DB"s I am at a lose.
If I am right then the "It" n your comment is the Execute SQL Task named Security Script
, that it is that query that continually executes against master. In order to connect to the database offered by the Foreach Loop each time that Execute SQL Task executes you have to dynamically change the Connection Manager the Execute SQL Task is using, namely BHMSQL2008.sa 1
. Pretty much every property of every object you can have in SSIS can be set to change its value at runtime based on an Expression. In this case the property of the Connection Manager BHMSQL2008.sa 1
you want to have change each time through the loop is the InitialCatalog.
If you highlight the connection, roght-click and go to Properties (or highlight and press F4) in the Expressions property click the ellipsis to open the Property Expressions Editor and change to look like this:
One other thing I noticed is that the Variable DatabaseName is setup to evaluate as an expression, but I do not think that is what you want. That variable is simply a place where the Foreach Loop will put the names into for use inside the loop as it iterates over the resultset retrieved in Execute SQL Task "Execute SQL Task - Get Databases". Open the Variables window, click on the DatabaseName variable and go to Properties (press F4). Blank out the Expression property and then change the EvaluateAsExpression property to False.
Thank should get you to the place where you are executing the SQL inside the "Security Script" Execute SQL Task against each database provided by the loop. If you get stuck again post back and we'll keep going.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato