SSIS ForEach Loop Container - loop through all DB's

Question

SSIS ForEach Loop Container - loop through all DB's

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

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 SSChampion Points: 13683 More actions · Answer 2

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 😛

MCSE SQL Server 2012\2014\2016

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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!!!!! 😛

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 😛

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 SSChampion Points: 13683 More actions · Answer 4

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!!!!! 😛
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 😛
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!

MCSE SQL Server 2012\2014\2016

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

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!!!!! 😛
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 😛
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 😛

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

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

Check your inbox O, I screwed it up...

MCSE SQL Server 2012\2014\2016

lkennedy76 SSChampion Points: 13683 More actions · Answer 7

It's the Database!!! (3/20/2013)
Check your inbox O, I screwed it up...

I got it, thankfully I had a Backup, WHEW:-D

MCSE SQL Server 2012\2014\2016

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

These may help:

Iterate Over Databases Using SSIS – Part 1[/url]

Iterate Over Databases Using SSIS – Part 2[/url]

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

lkennedy76 SSChampion Points: 13683 More actions · Answer 9

Thank you so much O! I got it working now. :w00t:

MCSE SQL Server 2012\2014\2016