Can anyone help how to write an SSIS Package to calculate folder size in one of the drive of my server...?
Hi All,
Could you please help me write the ssis package to calculate the sizes of all folder present in the drive..
. Regards
Prince Kher
January 18th, 2011 11:43am
In my opinion this is not a typical task for SSIS, why do you need this? This is rather an admin task.
Nevertheless, you should be able to use ForEach loop to go through all your folders, and inside the ForEach a Script Task would use some VB code like:
' Returns the sum of the files in the folder.
' dPath: Path of the directory
' include subfolders: set if include subfolders ;)
Public abort As Boolean
Function GetFolderSize(ByVal DirPath As String, ByVal includeSubFolders As Boolean) As Long
Try
Dim size As Long = 0
Dim diBase As New DirectoryInfo(DirPath)
Dim files() As FileInfo
If includeSubFolders Then
files = diBase.GetFiles("*", SearchOption.AllDirectories)
Else
files = diBase.GetFiles("*", SearchOption.TopDirectoryOnly)
End If
Dim ie As IEnumerator = files.GetEnumerator
While ie.MoveNext And Not abort
size += DirectCast(ie.Current, FileInfo).Length
End While
Return size
Catch ex As Exception
MsgBox("Error: " & ex.Message)
Return -1
End Try
End Function
To get the value to a variable (summarized) and then used elsewhere.
An example to the ForEach loop could be this post:
http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 18th, 2011 11:55am
The Code is throwing the error:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports
System
Imports
System.Data
Imports
System.Math
Imports
Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn(
"ScriptMain", Version:="1.0",
Publisher:="", Description:="")>
_
<System.CLSCompliantAttribute(
False)> _
Partial
Public
Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End
Enum
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Help, press F1.
Public
Sub Main()
' Returns the sum of the files in the folder.
' dPath: Path of the directory
' include subfolders: set if include subfolders ;)
Public abort
As
Boolean
Function GetFolderSize(ByVal
DirPath As
String,
ByVal includeSubFolders
As
Boolean)
As
Long
Try
Dim size
As
Long = 0
Dim diBase
As
New DirectoryInfo(DirPath)
Dim files()
As FileInfo
If includeSubFolders
Then
files = diBase.GetFiles(
"*", SearchOption.AllDirectories)
Else
files = diBase.GetFiles(
"*", SearchOption.TopDirectoryOnly)
End
If
Dim ie
As IEnumerator = files.GetEnumerator
While ie.MoveNext
And
Not abort
size +=
DirectCast(ie.Current, FileInfo).Length
End
While
Return size
Catch ex
As Exception
MsgBox(
"Error: " & ex.Message)
Return -1
End
Try
End
Function
Dts.TaskResult = ScriptResults.Success
End
Sub
End
Class
Pls help
January 26th, 2011 12:07pm
The below code is throwing sum error....
In my opinion this is not a typical task for SSIS, why do you need this? This is rather an admin task.
Nevertheless, you should be able to use ForEach loop to go through all your folders, and inside the ForEach a Script Task would use some VB code like:
' Returns the sum of the files in the folder.
' dPath: Path of the directory
' include subfolders: set if include subfolders ;)
Public abort As Boolean
Function GetFolderSize(ByVal DirPath As String, ByVal includeSubFolders As Boolean) As Long
Try
Dim size As Long = 0
Dim diBase As New DirectoryInfo(DirPath)
Dim files() As FileInfo
If includeSubFolders Then
files = diBase.GetFiles("*", SearchOption.AllDirectories)
Else
files = diBase.GetFiles("*", SearchOption.TopDirectoryOnly)
End If
Dim ie As IEnumerator = files.GetEnumerator
While ie.MoveNext And Not abort
size += DirectCast(ie.Current, FileInfo).Length
End While
Return size
Catch ex As Exception
MsgBox("Error: " & ex.Message)
Return -1
End Try
End Function
Plz help
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2011 12:10pm
What error does it generate?Arthur My Blog
January 31st, 2011 12:16pm