Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS ForEach Loop Container - loop through all DB's


SSIS ForEach Loop Container - loop through all DB's

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
I didn't quite get your comment. Are you stuck? What can I help with?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
lkennedy76
lkennedy76
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 769
opc.three (2/20/2013)
I didn't quite get your comment. Are you stuck? What can I help with?


Yes I am stuck. I understand what I need to do I just haven't ever wrote a query in variables.

MCSA SQL Server 2012
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
You'll need to use an Expression. In the Variables window, click on the Variable in question and hit F4 to bring up the Properties for that Variable. Set EvaluateAsExpression to True so that SSIS will know at runtime that it should re-evaluate the value of the Variable each time it is referenced:



Then, click in the Expressions property field just underneath the EvaluateAsExpression property and click the ellipsis on the right to open the Expression Editor. There you can enter your expression to build your SQL statement using any Variables in your Package:



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
eae.jpg (94 views, 15.00 KB)
ee.jpg (95 views, 50.00 KB)
lkennedy76
lkennedy76
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 769
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.

MCSA SQL Server 2012
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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.


No worries. We'll get you sorted. I'll have a look at the Package you sent me later tonight...

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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
lkennedy76
lkennedy76
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 769
Chicken bucket go BOOM! That worked, I am writing all information about (in requested script) all my databases to the table now! Thank you O!

however there is one last thing, when I run it from the SSIS it brings up a box that shows the DB name and I have to select OK for it to continue, how do I get rid of that so I can schedule this bad boy after I add 12 more instances...ha - ha :-P

MCSA SQL Server 2012
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
It's the Database!!! (3/20/2013)
Chicken bucket go BOOM! That worked, I am writing all information about (in requested script) all my databases to the table now! Thank you O!

That commerical should be banned from TV!!!!! :-P

Happy to help!

however there is one last thing, when I run it from the SSIS it brings up a box that shows the DB name and I have to select OK for it to continue, how do I get rid of that so I can schedule this bad boy after I add 12 more instances...ha - ha :-P

Just delete the Script Task in the FeLC. That was only added for debug purposes so you could see the values the FeLC was offering as it iterated over the resultset.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
lkennedy76
lkennedy76
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 769
opc.three (3/20/2013)
It's the Database!!! (3/20/2013)
Chicken bucket go BOOM! That worked, I am writing all information about (in requested script) all my databases to the table now! Thank you O!

That commerical should be banned from TV!!!!! :-P

Happy to help!

however there is one last thing, when I run it from the SSIS it brings up a box that shows the DB name and I have to select OK for it to continue, how do I get rid of that so I can schedule this bad boy after I add 12 more instances...ha - ha :-P

Just delete the Script Task in the FeLC. That was only added for debug purposes so you could see the values the FeLC was offering as it iterated over the resultset.


Sweet thanks! I love that part because that is something I would say!

MCSA SQL Server 2012
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
It's the Database!!! (3/20/2013)
opc.three (3/20/2013)
It's the Database!!! (3/20/2013)
Chicken bucket go BOOM! That worked, I am writing all information about (in requested script) all my databases to the table now! Thank you O!

That commerical should be banned from TV!!!!! :-P

Happy to help!

however there is one last thing, when I run it from the SSIS it brings up a box that shows the DB name and I have to select OK for it to continue, how do I get rid of that so I can schedule this bad boy after I add 12 more instances...ha - ha :-P

Just delete the Script Task in the FeLC. That was only added for debug purposes so you could see the values the FeLC was offering as it iterated over the resultset.


Sweet thanks! I love that part because that is something I would say!

That saying and part is funny and I like the girl but the guys in that commercial should all have to retire from acting :-P

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search