Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS ForEach Loop Container - loop through all DB's Expand / Collapse
Author
Message
Posted Friday, February 15, 2013 2:27 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
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
Post #1420771
Posted Friday, February 15, 2013 3:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 35,371, Visits: 31,915
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1420797
Posted Friday, February 15, 2013 3:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
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
Post #1420817
Posted Monday, February 18, 2013 7:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
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
Post #1421216
Posted Monday, February 18, 2013 11:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
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
Post #1421312
Posted Monday, February 18, 2013 2:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
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
Post #1421353
Posted Monday, February 18, 2013 2:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
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


  Post Attachments 
IterateOverDatabases.zip (27 views, 13.16 KB)
Post #1421354
Posted Tuesday, February 19, 2013 1:39 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
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

  Post Attachments 
AuditReport.zip (17 views, 37.90 KB)
Post #1421835
Posted Tuesday, February 19, 2013 1:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
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


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1421839
Posted Tuesday, February 19, 2013 2:34 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
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


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


MCSA SQL Server 2012
Post #1421854
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse