SSRS 2005 help - need to get file size from within TSQL (path specified in column). Anyone?

Question

I need to get the Folder size and display the info on a report (SSRS). I need to do this for a number of Databases (loop!). These DB's are websites' backends. Has anyone done this before? Can you please point me to some samples or right direction? Does xp_filesize and the like the right solution? Help!

Answer 1

Could you clarify who should do what in your scenario? Do you want SQL Server do get the info or do you want Reporting Server do that?

What exactly do you mean by "folder size"? Is "one folder, sum up each file" enough or does it need to be recursive? Either way, I'd go for a little custom .NET function that uses System.IO.Directory and it's relatives.

Answer 2

Looking at the question and Tomalak's response, and I'm assuming the reporting server will be able to reach the folders held in the DB:

Firstly set up the query to get you back the result-set of paths - I assume you'll have no trouble with this part. Next you'll need to add a custom code function to your report: http://msdn.microsoft.com/en-us/library/ms155798.aspx - This function will take the folder path as a parameter, and pass back the size of the folder. You'll have to write in VB.Net if you want to embed the code in the report, or you could code up a DLL and bring that in.

An example VB.Net code block (Remember you may need to prefix objects with System.IO.) http://www.freevbcode.com/ShowCode.asp?ID=4287

Public Shared Function GetFolderSize(ByVal DirPath As String, _
   Optional IncludeSubFolders as Boolean = True) As Long

  Dim lngDirSize As Long
  Dim objFileInfo As FileInfo
  Dim objDir As DirectoryInfo = New DirectoryInfo(DirPath)
  Dim objSubFolder As DirectoryInfo

Try

'add length of each file
  For Each objFileInfo In objDir.GetFiles()
    lngDirSize += objFileInfo.Length
  Next

  'call recursively to get sub folders
  'if you don't want this set optional
  'parameter to false 
If IncludeSubFolders then
  For Each objSubFolder In objDir.GetDirectories()
    lngDirSize += GetFolderSize(objSubFolder.FullName)
  Next
End if

Catch Ex As Exception


End Try

   Return lngDirSize
End Function

Now, in your report, in your table, you'd have for the cell that shows the folder size an expression something like:

=Code.GetFolderSize(Fields!FolderPath.Value)

I doubt this approach will be performant for a manually-viewed report, but you might get away with it for small result sets, or a scheduled report delivered by email?

Oh, and this piece suggests you 'may' run into permissions issues using System.IO from within RS: http://blogs.sqlxml.org/bryantlikes/pages/824.aspx

Answer 3

I'd consider splitting this into two pieces, maybe a Windows Service to scan the directories and aggregate the data into a database, then use SSRS to report on the database as usual.

The reason I suggest this is to use master..xp_filesize and it's kin the account the SQL Server service is starting with needs access to the paths to be scanned. Once this turns into accessing paths on other machines I'd be less comfortable with the security implications of that.

Hope this helps

Answer 4

In SSRS you can to do this with the help of custom data extension. U need give the path for the datasource as your folder name and it will retrive your files and its related informations and displayed

For further reference and custom dll use this http://www.devx.com/dbzone/Article/31336/0/page/4

I have done this earlier.

Note: you have to make related changes to Report Designer and Report Manager configuration files.