'************************************************************************
'  Getsize and free space information for each physical file
'  in all databases on a server and insert to a logging table
'
'  Expects 2 global variable
'    SQL Server 	- where the free space will be checked 
'    Log to SQL Server  - where the info will be logged
'
'  Requires stored procedures 
'    admin.dbo.SpaceUsedByFileGroupInser  
'       (procedure will create table admin.dbo.SpaceUsedHistory)
'    admin.dbo.SpaceUsedByFileGroupLowFree  
'  at the Log to SQL Server
'  
'  Bill Wunder
'************************************************************************

Option Explicit

' DTS doesn't know ado and I haven't figured out how to add an #include
' so provide local constants for all ado enums used 
Const adCmdStoredProc = 4
Const adInteger = 3 
Const adVarChar = 200
Const adBSTR = 8
Const adBoolean = 11
Const adDouble = 5
Const adParamReturnValue = 4
Const adParamInput = 1

Function Main()

  Dim sSQLServer, sLoggingSQLServer, sDB
  Dim oSQLServer, oDB, oFileGroup, oDBFile
  
  Dim adoConnection, adoCommand
  Dim sConnect
  
  Dim ReturnCode
  Dim Server, Database, FileGroup, FileName, File, Path, IsPrimaryFile, Size
  Dim SizeInKB, SpaceAvailableInMB, FileGrowth, FileGrowthType, MaximumSize
    
  ' get the global variables into local working variables
  sSQLServer = DTSGlobalVariables("SQL Server")
  sLoggingSQLServer = DTSGlobalVariables("Log to SQL Server")
    
  ' open a dmo connection the SQL Server to be be checked
  Set oSQLServer = CreateObject("SQLDMO.SQLServer")
    oSQLServer.LoginSecure = True 'trusted
    oSQLServer.Connect sSQLServer

  Set adoConnection = CreateObject("ADODB.Connection")

  sConnect = "Provider='sqloledb';Data Source='" & _
            sLoggingSQLServer & "';" & _
            "Integrated Security='SSPI';Initial Catalog='admin';"
                           
  ' open an ado connection to the Log to SQL Serer
  Set adoCommand = CreateObject("ADODB.Command")
          
  adoConnection.Open sConnect
    
  '  set uo the ado command object 
  With adoCommand
    .ActiveConnection = adoConnection
    .CommandText = "SpaceUsedByFileGroupInsert"
    .CommandType = adCmdStoredProc
    
    ' Set up a return parameter.
    Set ReturnCode = .CreateParameter("Return", adInteger, adParamReturnValue)
    .Parameters.Append ReturnCode

    'create named parameter objects so they can be reassigned for each file
    Set Server = .CreateParameter("@Server", adVarChar, adParamInput, 128, sSQLServer)
    .Parameters.Append Server
    Set Database = .CreateParameter("@Database", adVarChar, adParamInput, 128, Database)
    .Parameters.Append Database
    Set FileGroup = .CreateParameter("@FileGroup", adVarChar, adParamInput, 128, FileGroup)
    .Parameters.Append FileGroup
    Set FileName = .CreateParameter("@FileName", adVarChar, adParamInput, 128, FileName)
    .Parameters.Append FileName
    Set File = .CreateParameter("@File", adInteger, adParamInput, , File)
    .Parameters.Append File
    Set Path = .CreateParameter("@Path", adBSTR, adParamInput, 128, Path)
    .Parameters.Append Path
    Set IsPrimaryFile = .CreateParameter("@IsPrimaryFile", adBoolean, adParamInput, , IsPrimaryFile)
    .Parameters.Append IsPrimaryFile
    Set Size = .CreateParameter("@Size", adInteger, adParamInput, , Size)
    .Parameters.Append Size
    Set SizeInKB = .CreateParameter("@SizeInKB", adDouble, adParamInput, , SizeInKB)
    .Parameters.Append SizeInKB
    Set SpaceAvailableInMB = .CreateParameter("@SpaceAvailableInMB", adInteger, adParamInput, , SpaceAvailableInMB)
    .Parameters.Append SpaceAvailableInMB
    Set FileGrowth = .CreateParameter("@FileGrowth", adInteger, adParamInput, , FileGrowth)
    .Parameters.Append FileGrowth
    Set FileGrowthType = .CreateParameter("@FileGrowthType", adInteger, adParamInput, , FileGrowthType)
    .Parameters.Append FileGrowthType
    Set MaximumSize = .CreateParameter("@MaximumSize", adInteger, adParamInput, , MaximumSize)
    .Parameters.Append MaximumSize
  End With
  
  ' loop through all files in all databases in the DMO connected SQL Server
  ' and submit the file info to the ado connected Log to SQL Server
  For Each oDB In oSQLServer.Databases
    For Each oFileGroup In oDB.FileGroups
      For Each oDBFile In oFileGroup.DBFiles

        Database.Value = oDB.Name
        FileGroup.Value = oFileGroup.Name
        FileName.Value = oDBFile.Name
        File.Value = oDBFile.Id
        Path.Value = oDBFile.PhysicalName
        IsPrimaryFile.Value = oDBFile.PrimaryFile
        Size.Value = oDBFile.Size
        SizeInKB.Value = oDBFile.SizeInKB
        SpaceAvailableInMB.Value = oDBFile.SpaceAvailableInMB
        
        ' collapse now and recreaet in the proc
        If oDBFile.FileGrowthType = 0 Then
          FileGrowth.Value = oDBFile.FileGrowthInKB
        Else
          FileGrowth.Value = oDBFile.FileGrowth
        End If
        
        FileGrowthType.Value = oDBFile.FileGrowthType
        MaximumSize.Value = oDBFile.MaximumSize

        adoCommand.Execute

        ' any error will also implicitly stop execution here or at any other line
        If Returncode <> 0 Then
          Main = DTSTaskExecResult_Failure
          Err.Raise vbObjectError + 65535,"[" & Server.Value & "][" & Database.Value & "][" & FileGroup.Value & "][" & FileName.Value & "]", adoCommand.CommandText & " return code = " & ReturnCode.Value
        End If
      
      Next 'File
    Next 'FileGroup
  Next 'Database
        
  'all are logged so check for files with low free space and notify if any found
  With adoCommand
    .CommandText = "SpaceUsedByFileGroupLowFree"
    .CommandType = adCmdStoredProc
    
    ' Set up parameters (cheat since you want to use all procedure parameter defaults)
    .Parameters.Refresh

    .Execute
    ' any error will also implicitly stop execution here or at any other line
    If .Parameters(0).Value <> 0 Then
      Main = DTSTaskExecResult_Failure
      Err.Raise vbObjectError + 65535, .CommandText, " return code = " & .Parameters(0).Value
    End If

  End With

  ' clean up
  adoCommand.ActiveConnection = Nothing
  adoConnection.Close
  Set adoConnection = Nothing
  
  oSQLServer.DisConnect
  Set oSQLServer = Nothing
          
  ' if we get to here the step succeeded
  Main = DTSTaskExecResult_Success

End Function