Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSIS ForEach Loop Container - loop through all DB's Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 8:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1422382
Posted Friday, February 22, 2013 8:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:29 PM
Points: 277, Visits: 561
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
Post #1423090
Posted Friday, February 22, 2013 1:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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


  Post Attachments 
eae.jpg (75 views, 15.08 KB)
ee.jpg (76 views, 50.77 KB)
Post #1423229
Posted Tuesday, March 19, 2013 11:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:29 PM
Points: 277, Visits: 561
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
Post #1432819
Posted Tuesday, March 19, 2013 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1432887
Posted Tuesday, March 19, 2013 7:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1432972
Posted Wednesday, March 20, 2013 9:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:29 PM
Points: 277, Visits: 561
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


MCSA SQL Server 2012
Post #1433316
Posted Wednesday, March 20, 2013 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1433329
Posted Wednesday, March 20, 2013 10:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:29 PM
Points: 277, Visits: 561
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!


MCSA SQL Server 2012
Post #1433334
Posted Wednesday, March 20, 2013 10:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1433340
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse