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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics