SSIS Package to get Disk Space Info with WMI.

I have created package that gives Disk Space info using WMI Data Reader Task.
You need minor knowledge on WMI Query and WMI Class, here i have used “Win32_LogicalDisk” WMI class.
This package checks disk info for logical disk of your machine and stores value to the database.
To store information create one table on your database.

Use AdventureWorks2012
Go
Create Table ServerDiskInfo
(
Drive varchar(5),
DriveSize numeric(18,2),
FreeSpace numeric(18,2),
CollectionDate Datetime Not NUll Default GetDate()
)

Lets start to create SSIS package.
Step1 : Open your SSIS package and drag WMI Data Reader Task from SSIS Toolbox.

Step2 : Rigth click on Connection Manager pane –> Select New Connection –> Select WMI and click on ADD button.

Step3 : It will opens WMI Connection Manger Editor. you can give any name to connection name and discription.

If you are creating package for your local machine then

\\LocalHost to connect your local machine and check on Use Windows Authentication.

If you are creating package for remote server then

\\RemoteServerName to connect remote server and provide “UserName” and “Password” for User Credentials.

Click on Test button to confirm connetion established successfully.

Step4 : Right Click on WMI Data Reader Task –> Click on Edit –> WMI Options Pane.

Give your WMI Connection –> WMI_localmachine(WMI Connection Manager).

WQL Query Source Type –> Direct Input.

WQL Query Source –>Select Name, Size, FreeSpace From Win32_LogicalDisk Where DriveType = 3.

OUTPUT Type –>DataTable.

OverWrite Destination –> Overwrite Destination.

Destination Type –> File Connecion.

Destination –> Create New Connetion.

Click OK.

Click Ok.

Step5 : Execute task to generate C:\ServerDiskInfo.csv file.

Step6: Drag Data Flow task –> Right Click –> Edit.

Step7: Drag Flat File Source from Other Source Tool, Drag Data Conversion from Other Transforms, Drag OLEDB Destination from Other Destinations.

Step8 : Right Click on Flat File Source –> Edit

Click on Columns Tab to check the columns then click on OK.

Step9 : Right Click on Data Conversion –> Select FreeSpace and Size.

Give Numeric[DT_numeric] in Data Type to both columns.

Step10 : Right Click on OLE DB Connection –> Edit.

Select OLEDB connection manager. if there is no OLEDB Connection manager then create new connection manager for AdventureWorks2012 and Select ServerDiskInfo Table.

Step11: Select mappings from left pane.

map Input Columns with Destination Columns given in image.

Click OK.

Executing whole task it will make entry in ServerDiskinfo Table.

Query to get ServerDiskInfo.

declare @Today as datetime
set @Today = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
select Drive,Cast(DriveSize/1024/1024/1024 as Numeric(10,2)) as SizeInGB,Cast(FreeSpace/1024/1024/1024 as Numeric(10,2)) as FreeSpaceInGB from ServerDiskInfo
where DATEADD(DD,0,DATEDIFF(DD,0,CollectionDate)) = @Today

Regards,
Nirav Gajjar