By: Dale Kelly | Comments (6) | Related: More > Database Administration
Problem
One of my SQL servers has a database that grows very rapidly (1 GB per week). I needed an easy way to monitor the growth of this database. I also needed an easy way to monitor all my servers for growth trending. In a previous tip I covered the process I used to gather the data, in this tip I show how to import the data into a database using SSIS and report on it using SSRS.
Solution
In my tip Gather database physical file size using PowerShell I showed how to gather the data using PowerShell. In this tip I will show how I use SQL Server Integration Services (SSIS) to import the data from an Excel spreadsheet into a SQL server database. Then use SQL Server Reporting Services (SSRS) to report on the data. Using SSRS to report on the data I can very quickly see the trends for the physical data files of all the databases on all my SQL servers.
Importing the data from Excel with SSIS
I collect database file size using a PowerShell script as described in a previous tip. As I mentioned in the other tip I chose to put the data in an Excel spreadsheet, this was more for presentation purposes so the format of the spreadsheet is not exactly fit for importing. I manually change the spreadsheet to prepare it for importing. To prepare the spreadsheet, I change the heading for column A and fill in server name in that column then remove the Server row, Database heading row and blank row between each server. This is shown in the images below.
This process takes me about 5 minutes for 20 plus SQL Servers. I know it is possible to have the PowerShell script do this for me and leave that as an exercise for the reader. Once I have the format of the spreadsheet correct I save it as an Excel 2003 spreadsheet (.xls), I did this because some versions of SSIS do not support .xlxs. Now that my spreadsheet is in the proper format I am ready to import the data using SSIS.
My SSIS project is fairly simple with just 4 components to the data flow. The Excel Source, a Data Conversion, Derived Column and an OLEDB Destination as shown in the image below.
The Excel Source is the spreadsheet modified in the previous step. I include the Data Conversion component to ensure the data fields are set to the proper data type and length to match the database fields.
I add a single Derived Column for the Create date.
Then I have the OLEDB Destination. The connection is an OLE DB Connection to the server and database where the data is stored. The database has a single table with the structure shown below.
In the OLEDB Destination component I map columns from the source to the database table as shown.
Now that I have the data in a database I can use SQL Server Reporting Services to generate a report and very quickly see the growth trends for all my SQL Servers. The report I created is fairly simple with columns for database, data file, data file size and used space. I also include a chart for each server and at the end of the report I have a summary chart showing all servers. There are two datasets in the report, one for the weekly data and one for totals.
The final report shows the date the data was collected and totals for each server. The data can be expanded to show the values for each database as show in the image below. At the end of each server section is a chart showing all the databases and file size trends.
It may sound like a long involved process, but from beginning to end it takes less than 10 minutes and I very quickly see how my data files are growing. I also realize there are other ways to accomplish the data import, but this is the method I chose.
Next Steps
- Create your own database and import package and import your data.
- Create your report and run it.
- As always, be sure to test this process in a test environment before applying it to production.
- For more information check these references.
- Download a zip file with the SSIS and SSRS projects used in this tip.
About the author
Dale Kelly has been in the computer industry since the late 80s and is currently a Sr. DBA responsible for 26 SQL Servers.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
