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