SSIS ForEach Loop Container - loop through all DB's
-
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?
MCSE SQL Server 2012\2014\2016
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 -
Running a report on security per database per server.
O,
I will try that, I was leaning in this direction...
Thank you
MCSE SQL Server 2012\2014\2016
-
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??
MCSE SQL Server 2012\2014\2016
-
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 -
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 -
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.
MCSE SQL Server 2012\2014\2016
-
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:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
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:
I can tell you my writing skills are not where they should be on for a rewrite. 🙁
MCSE SQL Server 2012\2014\2016
-
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 -
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.
MCSE SQL Server 2012\2014\2016
-
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 -
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.
MCSE SQL Server 2012\2014\2016
-
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply