March 28, 2016 at 4:32 am
The following underneath code when try to(preview) run in the SSIS package throws an error.
I want this to run in the OLEDB Source Editor as a SQL Command.ATtached is the error message.The result of the select from the temp table (#output) should go into a SQL table.And I plan to run this statement as part of an SSIS package where the dataflow task will be part of a for each loop which is used to loop through a list of sql servers.Any responses are appreciated.What should be done to execute this.
Thanks
declare @svrName varchar(255)
declare @sql varchar(400)
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
CREATE TABLE #output
(line varchar(255))
insert #output
EXEC xp_cmdshell @sql
insert into dbo.ServerDriveSpace(ServerName,Drivename,TotalSpace,FreeSpace)
select @@Servername as ServerName,rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
from #output
where line like '[A-Z][:]%'
order by drivename
drop table #output
March 30, 2016 at 6:05 am
Hi
What is the main objective you are trying to achieve with the overall process? This seems complicated for what I think you are trying to do.
If you cycled around the severs and ran something like this then perhaps it would be easier
SELECT f.database_id,
DB_NAME(f.database_id),
f.file_id,
volume_mount_point,
file_system_type,
total_bytes / 1024 /1024 as TotalMB,
available_bytes / 1024 /1024 as AvailableMB,
f.size * 8 / 1024 as DataSizeMB
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) as s;
Rich
April 4, 2016 at 10:05 am
#temp tables only exist for the duration of the connection that created them.
Also, I guess that you want to store the results on a different server from where the xp_cmdshell executes.
Try having everything up to the Exec xp_cmdshell as the source in the data flow, a conditional split to exact the information you need and a destination of where you want to store the information.
Jez
April 13, 2016 at 12:48 am
I am trying to collect drives space details (total and available) from across a list of 150 servers using t-sql and SSIS with t-sql as part of the SSIS package.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply