February 15, 2016 at 3:26 am
How can I nmake this query run in SSIS data flow task (OLEDB Source)
declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
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+''*''}"'
--creating a temporary table
--drop table #output
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--script to retrieve the values in MB from PS Script output
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
--script to retrieve the values in GB from PS Script output
--select @@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)/1024,0) as 'capacity(GB)'
-- ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
-- (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
--from #output
--where line like '[A-Z][:]%'
--order by drivename
--script to drop the temporary table
drop table #output
select * from dbo.ServerDriveSpace
delete from dbo.ServerDriveSpace
I get the attached error wheh I check Preview in OLEDBSource.How can I get this to work.
Thanks
February 15, 2016 at 3:47 am
Does the query work if you run it in Management Studio?
If this is something you're going to do frequently (or even if it isn't), you might consider creating a permanent staging table in your database instead of using a temp table. Don't forget to truncate it at the beginning of the load.
John
February 15, 2016 at 11:12 am
Yes it works perfectly in management studio.
February 15, 2016 at 11:14 am
Cant create permanent DB objects.In SSIS I will be running the above query across many servers to gather the information as per the query details.
February 15, 2016 at 12:09 pm
SSIS won't work with temp tables. I'm not sure if table variables are an option or will cause the same error. The safest way is to create perm tables in all the servers.
February 15, 2016 at 11:10 pm
Permanent tables is not a option.I think SSIS works with temp table with some settings but no sure how...
February 16, 2016 at 12:13 am
I am not sure why are you involving xp_cmdshell in this. If you execute your command-line in an SSIS Execute Process Task and capture the output to a variable or a file you can obviate the need for both xp_cmdshell and any temp tables.
If I am reading your code correctly you're loading dbo.ServerDriveSpace, selecting the result, and then immediately clearing the table. Are you doing all this just so you can get the data back out to a Data Flow Task to send it into a table on another (maybe centralized) data-collection instance? Either way, you can do all this without even connecting to the SQL Server Instance. When you think about it, should we really need to access SQL Server to find out how full the disks are on the server it is running on? It's a pure Windows OS check at this point.
Another way:
1. Call PowerShell using an Execute Process Task and send the output to a CSV file. Lookup PowerShell cmdlet Export-Csv for an easy way to send your pipeline data to a CSV file.
2. Load the resulting CSV file wherever you need it to go using a Flat File Connection Manager to read it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply