You could set the connection string property dynamically inside ForEach Loop, just the database part would be different, I think.Arthur My Blog

Hey guys! I really want to use the following query to collect DB size info because in works in 2000, 2005 and 2008: SELECT name, size FROM sysfiles Problem is it only returns info based on the DB I'm using. I need to do this for all DBs in a instance. I have a list of DBs in my package that I want to run this against. Is there any way I can dynamically change the DB this runs against using parameters or something? Like: SELECT name, size FROM [?].dbo.sysfiles or maybe use [?] go SELECT name, size FROM sysfiles obviously the above doesn't work, but hopefully you get the picture of what im trying to do.

You can certainly do so and in several ways. A ForEach Loop fed by an Object variable for example seems to be a feasible solution. But it is good to know how did you create and keep the list of DBs in the package first?Arthur My Blog

Hey Arthuz, Would you use a script component for that? I already have this nested in a loop that changes the connection string to go to other instances so I'm weary of modifying it again in this way as well. I would prefer a solution using T-SQL with the "USE" command or something. Is there a basic transformation that I can use to just run that with the DB value? Otherwise please show me how you would implement your solution. Cheers!

This should do almost exactly what yo're looking for: Using the Foreach ADO Enumerator in SSIS (to modify a Connection Manager) Talk to me now on

Hi, Sweaty, sys.master_files will return everything in 2005, 2008. Can you use it?Garry

take a look at the undocumented stored procedure sp_msforeachdb

Not exactly what you are looking for, but my vote would be to use sp_MSforeachdb. Something like the following: CREATE PROCEDURE dbo.dbSize AS SET NOCOUNT ON DECLARE @cmd1 varchar(500) DECLARE @cmd2 varchar(500) CREATE TABLE #dbsize (dbname varchar(128), dbsize int) SET @cmd1 = 'USE ?' SET @cmd2 = 'INSERT INTO #dbsize SELECT name, size FROM ?.dbo.sysfiles' EXEC sp_MSforeachdb @command1=@cmd1, @command2=@cmd2 SELECT dbname, dbsize FROM #dbsize DROP TABLE #dbsize GO EXEC dbo.dbSize in Execute SQL Task. Cheers! Chaos, Disorder and Panic ... my work is done here!

Hey Guys, @Steve I can't use a stored procedure as the source of a data flow task. @Arthurz That was my thought too. To the point where i did that and then inside the loop I came to the problem above yet again! I have no way to reference the current loop iteration and sub it in as a parameter for the Source (because they don't support parameters) and I can't run a command like USE Database Before I run the source, and it can't be part of the source commands itselft either because it only works with 1 command queries. As for your question. I collect the DBs with the following command: SELECT NAME, GETDATE() AS LAST_SEEN, 0 AS SIZE FROM master.dbo.sysdatabases And after some manipulation it either inserts it into the DB or updates it's match in the current records. Then is uses a union all transformation to come back into 1 stream and into a ADO recordset destination that stores it in a global variable. From there it is easily used to iterate a loop through.

Hold on a sec - am I hearing you right that you're using (or trying to use) one variable for both an outer and inner loop? Don't do that - use two SSIS variables. If that's not it, please describe a little more how you have things set up. Talk to me now on

Hey Todd, No I'm not that crazy lol. I have 2 variables. In my source connection manager's connection string the expression looks like this: "Data Source=" + @[User::ConnectionString] + ";Initial Catalog=" + @[User::Catalog] + ";Provider=SQLNCLI10.1;Integrated Security=SSPI" The User::Catalog variable is new. That is the one I'm trying to populate with the DB name at the moment. It defaults to "msdb". So far I've been looping through my isntances successfully by switching the User::ConnectionString variable in a loop to the next instance in the list. Now I need my 2nd loop to iterate through the Database names and change the initial catalog in hopes that running my command against those specific databases will do the trick.

Hey Guys, @Garry: it also needs to work on 2000 as well @DaveBurk: unfortunately there are some things I need to do with the data beyond that and it will require that my commands be broken down into a data flow task. It's an intresting aproch to stay at the control flow level, but it won't work for me @todd: I am attempting your approch. I but my DB record set list (has some additional columns) into an global variable object and then run a second loop based on that. There is nothing in the loop at the moment, I just want to see it work 1st, but it breaks on the second instance at the point where i store the record set. I have a feeling it's because the global variable holding the record set of databases from the 1st instance isn't cleared before its attempted filling in the second instance. Is there a way I can reinitialize the global object variable in the 1st loop before it reaches the point it needs to get repopulated in?

Hey Guys, Sometimes I feel pretty stupid. I had the source pointing to my Destination connection manager not my source one. Now it is working and collecting acurate data until it gets about 4 instances in it hits a DB called "IderaSPDMRepository". It gives an error "Invalid Object Name 'SYSFILES'". If i go to this DB directly and run my query it gives the same error message. This is the 1st DB I have encountered with this problem. Does this mean this particular DB is broken?

Perhaps this is due to the lack of proper credentials. You may want to run DBCC CHECK DB just in case to make sure it is not corrupted.Arthur My Blog

Thanks for the additional explanation. Does the internal loop work once, or never? Talk to me now on

I got it working! It was actually changing the catalog variable I just didn't realize. It broke the second time around because it was no longer on MSDB. The only reason it worked the 1st time around was because the 1st data set happened to end on MSDB lol. Now I'm in the loop doing the real meat of the work. Is it safe to assume that the 2nd record returned from select * from SYSFILES is the log file, and that the first is the MDF file? If not, what assumptions can I make about the data within this table or view and what is the best way to collect the total size of the log and MDF file seperately if there are more or less than 2 records returned?

Sounds like that should work. So what's the problem? It shouldn't matter if the variable already contains a result set, it should get overwritten and reset by the Execute SQL Task... Talk to me now on

Only once

I discoverd upon closer inspection that sysfiles did indeed exsist for that DB. I found out that it was built using a Case-Sensitive collation so my upper case reference to it wasn't working. After changing my query to use lower case it worked for all the rest of the 2005 and 2008 instances! Now all that is left is to be able to run it against all the 2000 instances. I got through 2 of them before I got the following error: [Get Current DB size file info [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Unfortunately there were no earlier error messages. I know its not caused by any other part of the package because I have been able to run through all these instances before this addition. It breaks right on the OLE DB source for the only data flow task inside my 2nd loop. Funny thing is when i browse to the instance and DB that cause the problem and I try to explore the database it says The Database __________ is not accessible

Ok so heres where I'm stuck now. Inside my 2nd loop I get the DB size info with the following query: SELECT GROUPID, SIZE/128 AS SIZE FROM SYSFILES I use the Dirived Column transform to add the Catalog variable (holds the Current DB Name) and the current Instance ID (local to my documentation DB). I then do a conditional split. If GROUPID == 0 then I consider that the Log file. If GROUPID == 1 then I consider that the MDF file. Each of these goes to an OLE DB transform with the following queries: UPDATE TBL_DATABASES SET MEGABYTES_LOG = ? WHERE INSTANCE_ID = ? AND TITLE = ? The one for the MDF column is exactly the same it just points to the MDF column instead. I ran the package and it ran to completion which was very promising. Then I looked at the data and it put 7 for all the MDF values and 17 for all the log values for every db in every instance. That is obviously wrong, so what did I do wrong? Is it caching the results or something?

Hey Todd, To give you a more acruate picture of whats going on let me explain further. User::Catalog right now is not used. It is a string global default valued at 'msdb' for now. The start of my package obtains an instance list from a flat text file and stores it in a global variable of type object named User::ServerObject. The variable User::ConnectionString is then used as the current iteration of a for each loop through that list. Within that loop is the tasks that I conduct per isntance. Now after I process the things i need to do for the server and the instance I come to the part where i collect the specific database info. I have a recordset of 1 row per database (with other columns with additional info). Depending on if they are already in the destination DB or not I either insert or update the records. Then I use a Union All to bring the paths back together and store the recordset in a variable called User::Database_Sizer of type object. Now I want to run a second, nested foreach loop. I plan for it to use Database_Sizer much like the instance list above, and use User::Catalog as the current iteration holder. So the problem is this. Now that I have this 2nd loop, and I've got it looping through the list of DBs in the object variable there is nothing in the loop. User::Catalog is not changed from it's default. I just wanted to see it go through all the loops 1st before I continue to modify it but it breaks on the second instance. I think it might have something to do with the fact that I never clear the values out of the User::Database_Sizer variable so the second time it goes through the loop the variable is already populated. I never had this problem with the top loop and User::ServerObject because once that loop is done, the package is done. Thing is though, even if this does end up working whats next? how do I change what DB I'm using based on that User::Catalog variable before I run theOLE DB Source? I guess 2 data flow tasks? is just changing the value of that enough to change the DB I'm running against on the fly?