InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:08:54 GMT20http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxThank you so much O! I got it working now. :w00t:Mon, 25 Mar 2013 07:51:27 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxThese may help:[u][url=http://thesqldev.wordpress.com/2013/02/20/iterate-over-databases-using-ssis-part-1/]Iterate Over Databases Using SSIS – Part 1[/url][/u][u][url=http://thesqldev.wordpress.com/2013/03/23/iterate-over-databases-using-ssis-part-2/]Iterate Over Databases Using SSIS – Part 2[/url][/u]Sat, 23 Mar 2013 14:30:33 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]It's the Database!!! (3/20/2013)[/b][hr]Check your inbox O, I screwed it up...[/quote]I got it, thankfully I had a Backup, WHEW:-DWed, 20 Mar 2013 13:16:21 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxCheck your inbox O, I screwed it up...Wed, 20 Mar 2013 13:10:02 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]It's the Database!!! (3/20/2013)[/b][hr][quote][b]opc.three (3/20/2013)[/b][hr][quote][b]It's the Database!!! (3/20/2013)[/b][hr]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![/quote]That commerical should be banned from TV!!!!! :-PHappy to help![quote]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[/quote]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.[/quote]Sweet thanks! I love that part because that is something I would say![/quote]That saying and part is funny and I like the girl but the guys in that commercial should all have to retire from acting :-PWed, 20 Mar 2013 10:35:29 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]opc.three (3/20/2013)[/b][hr][quote][b]It's the Database!!! (3/20/2013)[/b][hr]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![/quote]That commerical should be banned from TV!!!!! :-PHappy to help![quote]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[/quote]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.[/quote]Sweet thanks! I love that part because that is something I would say!Wed, 20 Mar 2013 10:26:19 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]It's the Database!!! (3/20/2013)[/b][hr]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![/quote]That commerical should be banned from TV!!!!! :-PHappy to help![quote]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[/quote]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.Wed, 20 Mar 2013 10:17:47 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxChicken 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 :-PWed, 20 Mar 2013 09:38:41 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]It's the Database!!! (3/19/2013)[/b][hr]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.[/quote]If I am right then the "It" n your comment is the Execute SQL Task named [i]Security Script[/i], 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 [i]BHMSQL2008.sa 1[/i]. 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 [i]BHMSQL2008.sa 1[/i] 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:[img]http://www.sqlservercentral.com/Forums/Attachment13348.aspx[/img]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.Tue, 19 Mar 2013 19:42:07 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]It's the Database!!! (3/19/2013)[/b][hr]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.[/quote]No worries. We'll get you sorted. I'll have a look at the Package you sent me later tonight...Tue, 19 Mar 2013 13:51:39 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxI added the expression with no luck. It still puts master in serveral times and not the other DB"s I am at a lose.Tue, 19 Mar 2013 11:45:56 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxYou'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 [i]EvaluateAsExpression[/i] to True so that SSIS will know at runtime that it should re-evaluate the value of the Variable each time it is referenced:[img]http://www.sqlservercentral.com/Forums/Attachment13244.aspx[/img]Then, click in the [i]Expressions[/i] property field just underneath the [i]EvaluateAsExpression[/i] 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:[img]http://www.sqlservercentral.com/Forums/Attachment13245.aspx[/img]Fri, 22 Feb 2013 13:06:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]opc.three (2/20/2013)[/b][hr]I didn't quite get your comment. Are you stuck? What can I help with?[/quote]Yes I am stuck. I understand what I need to do I just haven't ever wrote a query in variables.Fri, 22 Feb 2013 08:18:54 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxI didn't quite get your comment. Are you stuck? What can I help with?Wed, 20 Feb 2013 20:08:56 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]opc.three (2/19/2013)[/b][hr]The one I attached to this thread does not write anything, it just selects and then pops a message box. I looked at the one you attached and the Execute SQL Task and Loop container look good plus I see you have the same debug Script Task in yours. In your Execute SQL Task named "Security Script" you need to change the SqlStatementSource to be built from an Expression, and in that Expression you need to make use of the Variable named User::DabaseName such that your query runs against a different database each time through the loop. At present it will simply execute the same query against whatever the default database is for the sa login on the BHMSQL2008 instance, I assume that's master.Here is a good intro on how to use Variables and an Expression to change what SQL is executed:[u][url=http://consultingblogs.emc.com/jamiethomson/archive/2008/12/08/making-the-case-for-expressions-instead-of-parameterised-sql-and-vice-versa-ssis.aspx]http://consultingblogs.emc.com/jamiethomson/archive/2008/12/08/making-the-case-for-expressions-instead-of-parameterised-sql-and-vice-versa-ssis.aspx[/url][/u][/quote]I can tell you my writing skills are not where they should be on for a rewrite. :(Tue, 19 Feb 2013 14:34:35 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxThe one I attached to this thread does not write anything, it just selects and then pops a message box. I looked at the one you attached and the Execute SQL Task and Loop container look good plus I see you have the same debug Script Task in yours. In your Execute SQL Task named "Security Script" you need to change the SqlStatementSource to be built from an Expression, and in that Expression you need to make use of the Variable named User::DabaseName such that your query runs against a different database each time through the loop. At present it will simply execute the same query against whatever the default database is for the sa login on the BHMSQL2008 instance, I assume that's master.Here is a good intro on how to use Variables and an Expression to change what SQL is executed:[u][url=http://consultingblogs.emc.com/jamiethomson/archive/2008/12/08/making-the-case-for-expressions-instead-of-parameterised-sql-and-vice-versa-ssis.aspx]http://consultingblogs.emc.com/jamiethomson/archive/2008/12/08/making-the-case-for-expressions-instead-of-parameterised-sql-and-vice-versa-ssis.aspx[/url][/u]Tue, 19 Feb 2013 13:53:28 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]opc.three (2/18/2013)[/b][hr]Attached is a sample SSIS 2008 R2 package that pops a MessageBox for each database on your instance.[/quote]O, I used both packages, the one you sent me on here, it only writes master to my table. Attached is the one I used. Tue, 19 Feb 2013 13:39:52 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxAttached is a sample SSIS 2008 R2 package that pops a MessageBox for each database on your instance.Mon, 18 Feb 2013 14:49:57 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]It's the Database!!! (2/18/2013)[/b][hr][quote][b]opc.three (2/15/2013)[/b][hr]1. Create 3 Variables- one of type Object that will contain the list of databases- one of type Int to contain the database_id- one of type String to contain the database name2. Add an Execute SQL Task that will select the database ids and names from sys.databases and store the resultset in the Variable of type Object3. Add a ForEach Loop Container after the Execute SQL Task and have it iterate over the Variable of type Object, mapping the id and name into the variables of type Int and String respectively. 4. up to you...do whatever you need to do inside the ForEach Loop Container.This approach can be extended to loop over database instances too. If you were to nest two loops you could loop over all databases in all instances of your choosing quite easily.[/quote]O, I put in the three Variables, DatabaseID (int32, Value 0)databases (object, system.object)Name (String, blank)then added my SQL task, Full Set, Select Name, Database_id from sys.databases, Result Set Parm Databases_id and NameI get this error message;[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".any guidance??[/quote]The Result Set page should only have one mapping. If you;re using OLE DB then the Resultset Name should be 0 (zero) and the variable should be your databases (object, system.object) variable.The parameter mapping to the two scalar variables will be done in your ForEach Loop Container which will drop the column-data from the DataTable into those variables, once for each row in the Result Set.Mon, 18 Feb 2013 14:47:22 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]opc.three (2/15/2013)[/b][hr]1. Create 3 Variables- one of type Object that will contain the list of databases- one of type Int to contain the database_id- one of type String to contain the database name2. Add an Execute SQL Task that will select the database ids and names from sys.databases and store the resultset in the Variable of type Object3. Add a ForEach Loop Container after the Execute SQL Task and have it iterate over the Variable of type Object, mapping the id and name into the variables of type Int and String respectively. 4. up to you...do whatever you need to do inside the ForEach Loop Container.This approach can be extended to loop over database instances too. If you were to nest two loops you could loop over all databases in all instances of your choosing quite easily.[/quote]O, I put in the three Variables, DatabaseID (int32, Value 0)databases (object, system.object)Name (String, blank)then added my SQL task, Full Set, Select Name, Database_id from sys.databases, Result Set Parm Databases_id and NameI get this error message;[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".any guidance??Mon, 18 Feb 2013 11:33:10 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxRunning a report on security per database per server.O, I will try that, I was leaning in this direction...Thank youMon, 18 Feb 2013 07:29:16 GMTIt's the Database!!!http://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx1. Create 3 Variables- one of type Object that will contain the list of databases- one of type Int to contain the database_id- one of type String to contain the database name2. Add an Execute SQL Task that will select the database ids and names from sys.databases and store the resultset in the Variable of type Object3. Add a ForEach Loop Container after the Execute SQL Task and have it iterate over the Variable of type Object, mapping the id and name into the variables of type Int and String respectively. 4. up to you...do whatever you need to do inside the ForEach Loop Container.This approach can be extended to loop over database instances too. If you were to nest two loops you could loop over all databases in all instances of your choosing quite easily.Fri, 15 Feb 2013 15:47:48 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspx[quote][b]It's the Database!!! (2/15/2013)[/b][hr]I am trying to get the FELC to loop through all the databases for my sql server. I want to set up a variable by database_id from the sys.databases table so the FELC knows to loop through all the databases. Has anyone wrote this before or have a different approach that I can try?[/quote]I know nearly nothing of SSIS but I have to ask because there may be an alternate solution. What is it that you're trying to do to each database?Fri, 15 Feb 2013 15:13:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1420771-1550-1.aspxI am trying to get the FELC to loop through all the databases for my sql server. I want to set up a variable by database_id from the sys.databases table so the FELC knows to loop through all the databases. Has anyone wrote this before or have a different approach that I can try?Fri, 15 Feb 2013 14:27:52 GMTIt's the Database!!!