SSIS Package to get Disk Space Info with WMI and Script Task.

In Previous blog i have created SSIS Package to get Disk Space Info with WMI.(Link: http://nirav.extreme-advice.com/2013/02/20/ssis-package-to-get-disk-space-info-with-wmi/)
That was a simple way to gather disk space information with importing data in CSV file and exporting data in database.
I have used same table in this task and i have used Script Task in behalf of CSV file.

Table Structure.

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 to get Disk Space Info with WMI and Script Task.

Step1 : Open your SSIS package and drag WMI Data Reader Task from SSIS Toolbox.

Step2 : Create new WMI Connection shown as below.

Step3 : Create variable Obj_Result with data type object to recieve result data.

Step4 : Select User::Obj_Result in Destination.

Step5 : Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

Step5 : Click on Edit on Script Task Editor and paste below code.

public void Main()
 {
            try
            {
                DataTable DT;
                DataRow DR;
                SqlConnection SQLCon = new SqlConnection("Server=Nirav'sDiary;Database=AdventureWorks2012;User Id=sa;Password=pwd;");

                DT = (DataTable)Dts.Variables["User::Obj_Result"].Value;
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string Name;
                    int Size, FreeSpace;
                    Name = DR["Name"].ToString();
                    Size = Convert.ToInt32(Convert.ToDouble(DR["Size"].ToString()) / 1024 / 1024 / 1024);
                    FreeSpace = Convert.ToInt32(Convert.ToDouble(DR["FreeSpace"].ToString()) /1024/ 1024 / 1024);
                    SqlCommand cmd = new SqlCommand("Insert Into ServerDiskInfo Values('"+ Name +"',"+ Size +","+ FreeSpace +",GetDate())", SQLCon);
                    SQLCon.Open();
                    cmd.ExecuteNonQuery();
                    SQLCon.Close();
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
 }

Step6 : Executing whole task it will make entry in ServerDiskinfo Table.

Query to get ServerDiskInfo.

Declare @Today as datetime
Det @Today = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
Delect 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</pre>

Regards,
Nirav gajjar