In this installment of the drive info series we will gather the drive information via a DTS package. Part 1 of this series used the File System Object from the Windows Scripting Host via the sp_OA suite of stored procedures to gather the drive information. The File System Object will be used again to accomplish this task, but this time from an ActiveXScript task. Using this method can only collect drive information from the server where this DTS package is run. The drive information can be stored on any SQL Server.

Open a new DTS package in the DTS Designer and create a new Connection object that points to a SQL Server where the drive information is going to be stored. Set the database to tempdb or the database that will hold the DriveInfo table which will be created in the package.

Create a new string global variable named DriveInfoXml.

The next thing that needs to be done is create the table to hold the data. Drag an Execute SQL task into the DTS Designer and add the following SQL statement.

IF NOT EXISTS(SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_NAME = 'DriveInfo')
CREATE TABLE dbo.DriveInfo
( Drive VARCHAR(255)
, DriveSize FLOAT
, FreeSpace FLOAT)

Here is a screen shot of the Execute SQL task set up.

Now we need to collect the drive information. Collection of the drive information is accomplished using the File System Object from an ActiveX Script task. The script accomplishes the same thing as the sp_OA sql script, but looks less like dynamic sql and doesn’t use xp_fixeddrives. The script stores the drive information in XML formatted data to be used in the later steps which insert the data in the table. Drag an ActiveX Script task into the DTS Designer, name it “Get Drive Info” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Create DriveInfo Table Execute SQL task to the Get Drive Info ActiveX Script task.

'**********************************************************************
'  Get Drive Size
'
'**********************************************************************
Function Main()
Dim FSO
Dim Drives
Dim Drive
Dim XML
' Set up global data.
Const DriveTypeRemovable = 1
Const DriveTypeFixed = 2
Const DriveTypeNetwork = 3
Const DriveTypeCDROM = 4
Const DriveTypeRAMDisk = 5
NewLine = Chr(10)
Set FSO = CreateObject("Scripting.FileSystemObject")
 Set Drives = FSO.Drives
  'Create an XML doc to use in SQL
XML = "<drives>"
For Each Drive In Drives
If Drive.DriveType = DriveTypeFixed Then
XML = XML & "<drive DriveLetter=""" & Drive.DriveLetter & """ TotalSize=""" & Drive.TotalSize & """ FreeSpace=""" & Drive.FreeSpace & """/>"
End If
Next
XML = XML & "</drives>"
DTSGlobalVariables("DriveInfoXml").Value = XML
MsgBox(XML)
Main = DTSTaskExecResult_Success
End Function

Here is a screen shot of the Get Drive Size ActiveX Script Task.

We use an ActiveX Script task for the next step that builds the sql command for the Execute SQL task to follow. The XML data created previous step is used in a sql script that utilizes the XML features of SQL Server. There are many ways to get this data into a table in a DTS package, but I choose XML just to show another example that didn’t fall back on Ado. I also think it’s kind of silly to use Ado in DTS when there are already database connections to use. It’s probably a little easier to debug as the output of this task can be copied into a query tool and tested.

Drag another ActiveX Script task into the DTS Designer, name it “Build Put DriveInfo” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Get Drive Info ActiveX Script task to the Build Put DriveInfo ActiveX Script task.

'**********************************************************************
'  Build Put DriveInfo
'************************************************************************
Function Main()
Dim SQL
Dim oPkg
Set oPkg = DTSGlobalVariables.Parent
SQL = "DECLARE @hdoc int"  & VbCrLf &_
"DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher"  & VbCrLf &_
"SET @doc ='" & DTSGlobalVariables("DriveInfoXml").Value & "'"  & VbCrLf &_
"--Create an internal representation of the XML document."  & VbCrLf &_
"EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc"  & VbCrLf &_
"-- Execute a SELECT statement that uses the OPENXML rowset provider."  & VbCrLf &_
"INSERT INTO dbo.DriveInfo"  & VbCrLf &_
"SELECT DriveLetter"  & VbCrLf &_
"     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)"  & VbCrLf &_
"     , FreeSpace / POWER(1024, 2)"  & VbCrLf &_
"  FROM OPENXML (@hdoc, '/drives/drive',1)"  & VbCrLf &_
"          WITH (DriveLetter varchar(255)"  & VbCrLf &_
"               ,TotalSize float"  & VbCrLf &_
"               ,FreeSpace float)"
oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = SQL
Main = DTSTaskExecResult_Success
End Function

Here is a screen shot of the Build Put DriveInfo ActiveX Script task.

The final step in the DTS package is to add the Put DriveInfo Execute SQL Task. The initial sql command is SELECT GETDATE() just to make the parser happy. The actual sql command to execute was generated by the previous step. Here is a sample of the sql command generated by a test run.

DECLARE @hdoc int
DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher
SET @doc ='<drives><drive DriveLetter="C" TotalSize="500000878592" FreeSpace="50790670336"/></drives>'
--Create an internal representation of the XML document.
EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
INSERT INTO dbo.DriveInfo
SELECT DriveLetter
     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)
     , FreeSpace / POWER(1024, 2)
  FROM OPENXML (@hdoc, '/drives/drive',1)
          WITH (DriveLetter varchar(255)
               ,TotalSize float
               ,FreeSpace float)

Here is a screen shot of the completed package.