I faced the challenge of retrieving information about the size of different databases on different servers and the disk space left on the server. Basically I wanted to find a way how I can retrieve this data periodically so I could monitor the growth of the databases and the disk space left.
I decided to create an SSIS package from this that loops through the servers and databases to get the information out of it from one single package. In this post I’ll describe how I set this up. In case you’re facing a similar issue, bear in mind that you can basically get every piece of information about the databases as long as you can query it. I’ll stick to the basics to show how it works, I won’t go too much into detail which different data you can get out of it.
I’ll split the whole thing in parts so it’s well readable… the first couple of tasks look like this:
Before I start with the tasks in the package, I have created an OLE DB connection with a random server name and database. The location of the server and database name are really not important here because they will be changed later on.
The first task is a very straightforward task that retrieves all the SQL Servers where you want to retrieve the database information from. For now I just hardcoded the servers, you could have a table somewhere where these servers are listed.
Some settings that are important here is that on the general page, the ResultSet is set to Full result set. On the page Result Set, add one result set with the name 0 (zero) and in the variable name, select a variable that you have create before (the variable needs to be of type Object!).
Right after this package I have added a foreach loop container. This container has an ADO Enumerator with the result set variable from the previous package selected. On the section Variable mappings, add a variable of type String, that holds the name of the server in each iteration of this loop. The variable that I have used here is called “Server”.
The next package is a script task, and this task is responsible for setting the connection to the right server in each iteration. It’s a VB .NET script package, and you have to make sure that the variable “Server” is on the ReadOnly list.
Edit the script, and I have added the following code:
Dim ConnMgr As ConnectionManager = Dts.Connections(“ds.ServerName.master”)
Dim ConnStr As String = ConnMgr.ConnectionString
ConnStr = “Provider=SQLNCLI10.1;Data Source=” + Dts.Variables(“Server”).Value.ToString + “;Integrated Security=SSPI;Initial Catalog=master”
ConnMgr.ConnectionString = ConnStr
Dts.TaskResult = ScriptResults.Success
This code basically sets the connection manager that you have created before with a connection string for the different servers in each iteration.
Back to the control flow… the next package is an SQL task that retrieves all the databases from the server. This package has similar settings to the server, also a full result set in an object variable but then for the different databases on the server.
Time for the second part of the control flow:
In the tempdb of the server, I have created a table that holds the physical disk space of the server. The first task in the screenshot above creates the table, the second task inserts the data with the following statement:
INSERT INTO [tempdb].[dbo].PhysicalDisk
In my original setup I had an OLE source connection in the data flow task that retrieved the disk space with the EXEC xp_fixeddrives statement, but apparently SSIS didn’t like that very much… therefore I changed it to this setup, mainly because I couldn’t find a way to use a source connection component that executes a stored procedure.
The next step is a loop through the databases, and for each database, the data flow task is executed. The data flow task looks like this:
On the left side, several pieces of information about the database is retrieved, like the logical name, physical name, size, etc. Since this query is different for every loop, I have put the query in an expression variable.
On the right side, I retrieve the physical disk space that I have added in the previous steps in the control flow.
I’ve joined this information together, and just for testing purposes I have put this in a text file. Eventually I’ll put a database connection as the destination in order to keep track of the database size.