Getting Drive Info, Part 4, the SSIS Script Component

In this installment of the Getting Drive Info series (Part 1, Part 2, Part 3), SSIS will be used to collect and save the drive information. SSIS provides multiple ways to accomplish this task. This installment will focus on using the Script Component to collect the drive information.

The Script component will be used as the source of a Data Flow where the DriveInfo class of the System.IO namespace will be used to collect the drive information. The DBA_Tools database and DriveInfo table from the earlier installments will also be needed.

Fire up BIDS and create a new project. Name the package something appropriate.

Drag a Data Flow task onto the Control Flow designer and name it DriveInfo.

Create a new Connection Manager that points to the DBA_Tools database on your SQL Server.

Bring the Data Flow designer into focus. Grab the Script Component from the Data Flow Transformations of the Toolbox and drag it onto the Data Flow designer. The Select Script Component Type dialog will appear. The Script Component can be a source of data, a transformer of data or a consumer of data. Choose Source, since this Script Component is going to provide drive information and click OK. Rename the Script Component Get DriveInfo.

Double click on the Script Component to open the editor. Now let’s add some output columns. Rename Output 0 to DriveInfo and add the columns:

• Drive, data type string [DT_STR]
• DriveSize, data type float [DT_R4]
• FreeSpace, data type float [DT_R4]

Here are the column property details for the Drive column.

It is important to define the output columns first so they show up OutputBuffer object while the coding the script. The output buffer will be named DriveInfoBuffer in this case.

Now it’s time to add the code to get the drive information and send ti to the output buffer. Here is the code in VB.Net for SSIS 2005.

Imports System
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        For Each di As DriveInfo In DriveInfo.GetDrives()
            If di.IsReady And di.DriveType = DriveType.Fixed Then
                Me.DriveInfoBuffer.AddRow()
                Me.DriveInfoBuffer.Drive = di.Name
                Me.DriveInfoBuffer.DriveSize = di.TotalSize
                Me.DriveInfoBuffer.FreeSpace = di.TotalFreeSpace
            End If
        Next

    End Sub

End Class

Here is the code in C# for SSIS 2008.

Make sure to set the ScriptLanguage property to Microsoft Visual C# 2008 in BIDS 2008 before clicking the Edit Script… button and pasting in the code below.

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */
        foreach (DriveInfo di in DriveInfo.GetDrives())
        {
            if (di.IsReady && di.DriveType == DriveType.Fixed)
            {
                DriveInfoBuffer.AddRow();
                DriveInfoBuffer.Drive = di.Name;
                DriveInfoBuffer.DriveSize = di.TotalSize;
                DriveInfoBuffer.FreeSpace = di.TotalFreeSpace;
            }
        }
    }
}

Now we need to add the destination to store the drive information. Grab the OLE DB Destination from the Data Flow Destinations of the Toolbox and drag it onto the Data Flow designer and name it Put DriveInfo. Connect the output of the Get DriveInfo Script Component to Put DriveInfo.

In the OLE DB Destination Editor, set the OLE DB connection manager to the DBA_Tools connection created earlier and pick the DriveInfo table from the table or view drop down.

Here is the create table statement for the DriveInfo table used in the earlier parts of the series.

CREATE TABLE dbo.DriveInfo
( Drive VARCHAR(255)
, DriveSize FLOAT
, FreeSpace FLOAT)

Now you are ready to start collecting and saving the drive information on your SQL Server. I will leave it to you to explore the DriveInfo object to see what other bits of information that may be useful to collect and store for the drives. The only limitation of this technique is that the package can only get the drive information on the server that the package is running on. The package would need to be deployed and run on every SQL Server where it is desried to collect the drive information. The last part of this series will address this limitation by implementing the collection of drive information using the WMI Task in SSIS.