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.
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".
MCSA SQL Server 2012