SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS ForEach Loop Container - loop through all DB's


SSIS ForEach Loop Container - loop through all DB's

Author
Message
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 919
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?

MCSA SQL Server 2012
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87894 Visits: 41124
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?


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?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15079 Visits: 14396
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.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 919
Running a report on security per database per server.

O,

I will try that, I was leaning in this direction...

Thank you

MCSA SQL Server 2012
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 919
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.


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??

MCSA SQL Server 2012
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15079 Visits: 14396
It's the Database!!! (2/18/2013)
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.


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??

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.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15079 Visits: 14396
Attached is a sample SSIS 2008 R2 package that pops a MessageBox for each database on your instance.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
IterateOverDatabases.zip (77 views, 13.00 KB)
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 919
opc.three (2/18/2013)
Attached is a sample SSIS 2008 R2 package that pops a MessageBox for each database on your instance.


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.

MCSA SQL Server 2012
Attachments
AuditReport.zip (57 views, 37.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15079 Visits: 14396
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:BigGrinabaseName 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

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
lkennedy76
lkennedy76
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 919
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:BigGrinabaseName 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


I can tell you my writing skills are not where they should be on for a rewrite. Sad

MCSA SQL Server 2012
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search