January 7, 2009 at 12:25 pm
All,
I am new to SSIS and I want to run a simple query again all the databases in my server. I know that i can use [sp_msforeachdb or cursors] but i dont want to use them.
My package is multi server package and i am stuck at one point where i want to run single query against all the databases on my server and bring the result back to the repository database.
Please let me know how i can do this.
January 7, 2009 at 2:07 pm
This one is fairly straight forward. In fact, there is a good exampe out on Pragmatic Works web site that I don't have time to locate for you. Here's what I would do:
1. Create a table in your Repository DB to hold the Instance and DB names of all of the DBs you want to check.
2. Start with an Execute SQL task to query that table.
3. Use the For Each Container to cycle through the host information in your table.
4. Run an Execute SQL task inside your container for your simple SQL statement.
You'll need to create 2 connections. One for your Repository DB and one for your Source DB. The key to this is that you'll need to get the values from your query in #1 above into package level variables and then use those package level variables to dynamically change your Source DB connection so that each iteration of your For Each Container runs the SQL against a different DB.
January 7, 2009 at 2:22 pm
Thanks John. I was doing some thing but you have me good approch. Let me try and i'll let you know by the results.
Thanks. Arman
January 7, 2009 at 2:54 pm
John, I did the same thing but no luck..
I dont know how to change the package level variables to dynamically change your Source DB
with in the For Each Container.
One more thing i am using the same for each loop container to run on the multiple servers and it was assigned by the variables.
January 7, 2009 at 3:16 pm
OK, sorry. Here's a bit more info into the interworkings of this.
First, the Result Set tab on your Execute SQL task to get your hosts needs to be set to an package variable of type Object. This will allow you to store the recordset from your SQL statement in an ADO recordset in memory.
Next, on the Collection tab on your For Each container, set the Enumerator to 'Foreach ADO Enumerator' and the ADO object source variable to the variable from the Execute SQL task. Use the default Enumeration mode of 'Rows in the first table'. This tells the For Each container that it needs to execute one time for each item in the collection.
Then, on the Variable Mappings tab of the For Each container, set your variables (which you'll need to create) to the index values from the query. This will store the changing values in variables each time the For Each Container iterates.
Let's give you an example. Let's say your set up table in your Repository DB looks like this:
CREATE TABLE dbo.Hosts (
HostID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Hosts PRIMARY KEY,
InstanceName varchar(50) NOT NULL,
DatabaseName varchar(50) NOT NULL,
IsActive bit NOT NULL
)
INSERT INTO dbo.Hosts(InstanceName, DatabaseName, IsActive)
SELECT 'MySQLInstance1', 'DatabaseA', 1 UNION ALL
SELECT 'MySQLInstance1', 'DatabaseB', 1 UNION ALL
SELECT 'MySQLInstance2', 'DatabaseA', 1 UNION ALL
SELECT 'MySQLInstance2', 'DatabaseB', 0
You'd set up your Execute SQL task with the following SQL statement:
SELECT InstanceName, DatabaseName FROM dbo.Hosts WHERE IsActive = 1
You'd now have 3 rows in your recordset object variable. The For Each Container's Variable Mappings tab would set your Instance variable to index value 0 and the Database variable to index value 1. Upon the first iteration of the For Each Container, your Instance variable should hold the value 'MySQLInstance1' and your Database variable should hold the value 'DatabaseA'.
Now, to tie this all together, right-click the SourceDB connection from the Connection Manager and select Properties. From the Properties window (usually docked in the lower right-hand corner of BDS), select Expressions. Here, you can set the ServerName property to your Instance variable and the InitialCatalog property to your Database variable.
Hopefully this helps.
January 7, 2009 at 3:20 pm
Once Again !!
Thanks John for your time and efforts.
January 7, 2009 at 7:46 pm
I am really Thanks full to you. Finally i did it.
January 8, 2009 at 9:50 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply