It's the Database!!! (2/15/2013)

---

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?

opc.three (2/15/2013)

---

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 name
2. 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 Object
3. 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.

It's the Database!!! (2/18/2013)

---

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 Name

I get this error message;

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".


any guidance??

opc.three (2/18/2013)

---

Attached is a sample SSIS 2008 R2 package that pops a MessageBox for each database on your instance.

opc.three (2/19/2013)

---

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:

http://consultingblogs.emc.com/jamiethomson/archive/2008/12/08/making-the-case-for-expressions-instead-of-parameterised-sql-and-vice-versa-ssis.aspx