However, I noticed when you use this on different SQL servers with different level of sensitivities/collations, you may get errors, since columns name is different in create statement and the queries used(some capital some small), same is related to system stored proc sp_MSforeachdb, I had to change to exact name as it's in master database and it let me run it on any of my servers smoothly..
Also for checking disk space is not accurate to use master.dbo.xp_fixedDrives, which is not showing mounting points info.
What I'm using for checking accurate disk space - WMI Data reader task,
this does not require enabling OLA Authomation if you have security restriction ...
There you may use this query:
Select Capacity,FreeSpace,DriveLetter,Label,SystemName From Win32_Volume Where DriveType = 3
and dump info in temporary text file, from which you can load this data in your central location and provide appropriate data massage
Here's query example from destination table:
SELECT [SystemName] as [System Name]
,case len(DriveLetter )
when 0 then 'Mounting Point'
else DriveLetter end as DriveLetter
,[Label] as Label
,round(convert(float,[Capacity])/1024/1024/1024,2) as [Capacity in GB]
,round(convert(float,[FreeSpace])/1024/1024/1024,2) as [Free Space in GB]
(convert(float,[Capacity])/1024/1024/1024)*100.0,2) as [% of available free space]
FROM [myDB].[dbo].[Disk space report]
Hope this is help..
Found a bug in code so far..
Collect server info in dataflow task:
Load Server Info contain wrong code:
WHEN Serverproperty('EngineEdition') = 1 THEN 'Integrated security'
WHEN Serverproperty('EngineEdition') = 2 THEN 'Not Integrated security'
WHEN Serverproperty('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security'
WHEN Serverproperty('IsIntegratedSecurityOnly') = 0 THEN 'Not Integrated security'
P.S> If anyone is interested to extend reports and use additional criteria - WMI Data Reader Task may help..
Press any key to continue or any other key to exit...